höchste und zweithöchste ID ermitteln

[Knappe]

Hallo,

ich möchte aus einer Tabelle die beiden höchsten IDs (Primärschlüssel) bestimmen, die zu einer größeren Anzahl an Datensätzen mit gemeinsamen Fremdschlüssel gehören.
Und diese beiden Datensätze möchte ich für eine Differenz nutzen, also Wert des Satzes mit der höchsten ID minus Wert des Satzes mit der zweithöchsten ID.

Beispiel:
ID FK Wert
1 1 12
2 2 8
3 3 5
4 1 18
5 4 3
6 2 11
7 1 23
8 3 9


Ich möchte also zum FK = 1 das Ergbnis aus 23 - 18 errechnen, denn
23 ist der Wert zur ID 7 und die wiederum ist die höchste ID zum FK 1 und
18 ist der Wert zur zweithöchsten ID (nämlich 4) zum FK 1.

Kann mir jemand einen (simplen) Trick verraten, wie ich das bewerkstelligen kann?

Danke vorab,
Ralf

 

[ukulele]

Also in MSSQL würde es so gehen:

SELECT    t1.id,
        t1.fk,
        t1.wert - isnull(t2.wert,0) AS wert
FROM    (

SELECT    ROW_NUMBER() OVER (PARTITION BY fk ORDER BY id DESC) AS zeile,
        id,fk,wert
FROM    tabelle

) t1
LEFT JOIN (

SELECT    ROW_NUMBER() OVER (PARTITION BY fk ORDER BY id DESC) AS zeile,
        id,fk,wert
FROM    tabelle

) t2
ON        t1.fk = t2.fk
AND        t2.zeile = 2
WHERE    t1.zeile = 1

Ich glaube MySQL kann auch ROW_NUMBER() aber eventuell etwas andere Syntax. isnull() müstest du auch noch ersetzen, dann könnte es so gehen.

 

[akretschmer]

ich möchte aus einer Tabelle die beiden höchsten IDs (Primärschlüssel) bestimmen, die zu einer größeren Anzahl an Datensätzen mit gemeinsamen Fremdschlüssel gehören.
Und diese beiden Datensätze möchte ich für eine Differenz nutzen, also Wert des Satzes mit der höchsten ID minus Wert des Satzes mit der zweithöchsten ID.

Das ginge mit teuren DB-Systemen wie Oracle oder M$SQL oder der OpenSource-DB PostgreSQL mit Window-Funktionen (lag() in diesem Falle) sehr, sehr einfach. Mit MySQL nicht, das kann kaum etwas, was man 2015 von einer Datenbank erwarten kann.

Warum nutzt man MySQL, wenn das, was man machen will, damit nicht geht? Klassische Fehlentscheidung.

 

[Knappe]

Wow, "simpel" ist anders

Ich werde mich gleich mal mit Deinem Hinweis beschäftigen und versuchen, da durchzusteigen. Auf jeden Fall schon einmal herzlichen Dank für die Antwort.

 

[Knappe]

Das ginge mit teuren DB-Systemen wie Oracle oder M$SQL oder der OpenSource-DB PostgreSQL mit Window-Funktionen (lag() in diesem Falle) sehr, sehr einfach. Mit MySQL nicht, das kann kaum etwas, was man 2015 von einer Datenbank erwarten kann.

Warum nutzt man MySQL, wenn das, was man machen will, damit nicht geht? Klassische Fehlentscheidung.

...nun, das liegt daran, dass ich eine Weiterbildungsmaßnahme über die Arbeitsagentur mache und da halt MySQL vorgegeben wird

 

[Knappe]

@ukulele:

du hast bei t1 und t2 jeweils den Alias "zeile" verwandt. Klappst das so oder müssten die Aliase verscheiden heißen?

 

[Distrilec]

@Knappe Das klappt so... Sind ja verschiedene Tabellen-Aliase vergeben (t1 und t2)

 

[ukulele]

Richtig, solange du immer explizite Schreibweisen wie t1.zeile oder t2.zeile verwendest wirst du keine Probleme bekommen.

 

[akretschmer]

Kann mir jemand einen (simplen) Trick verraten, wie ich das bewerkstelligen kann?

Danke vorab,
Ralf

Um das mal für Deine Daten zu zeigen:

Du hast:

test=*# select * from knappe ;
 id | fk | wert
----+----+------
  1 |  1 |  12
  2 |  2 |  8
  3 |  3 |  5
  4 |  1 |  18
  5 |  4 |  3
  6 |  2 |  11
  7 |  1 |  23
  8 |  3 |  9
(8 rows)

Nun spielen wir mal a bissl mit Window-Funktionen, hier lead(), lag() und row_number()

test=*# select fk, wert, lag(wert) over(partition by fk order by wert desc), wert - lead(wert) over (partition by fk order by wert desc) asresultat, row_number() over (partition by fk order by wert desc) from knappe ;
 fk | wert | lag | resultat | row_number
----+------+-----+----------+------------
  1 |  23 |  |  5 |  1
  1 |  18 |  23 |  6 |  2
  1 |  12 |  18 |  |  3
  2 |  11 |  |  3 |  1
  2 |  8 |  11 |  |  2
  3 |  9 |  |  4 |  1
  3 |  5 |  9 |  |  2
  4 |  3 |  |  |  1
(8 rows)

Nun willst Du ja nur je FK das resultat:

test=*# select fk, resultat from (select fk, wert, lag(wert) over(partition by fk order by wert desc), wert - lead(wert) over (partition byfk order by wert desc) as resultat, row_number() over (partition by fk order by wert desc) from knappe ) foo where row_number = 1;
 fk | resultat
----+----------
  1 |  5
  2 |  3
  3 |  4
  4 |
(4 rows)

Einfach, oder?