Order By Group By

[alexfrenzel92]

Hallo,

Meine Tabelle hat 4 Spalten (SpielerID, Gesamtstärke, Position, Priorität). Alle Spalten sind Integer.

Kann ich diese Tabelle zuerst nach einem bestimmten Schema sortieren und dann auf den jeweils nach dieser Sortierung mit dem jeweils höchsten Wert auf 2 Spalten gruppieren.

Das Ergebnis wäre eine neue Tabelle, in der jede SpielerID und jede Position nur einmal vorkommt. Haben in der sortierten Tabelle zwei Spalten die gleiche SpielerID oder die gleiche Position soll die Zeile genommen werden, die der Sortierung zufolge zuerst kommt.

ich hoffe, ich habe es verständlich und eindeutig formuliert...
Geht das irgendwie?

mfg und vielen Dank im vorraus
Alex

 

[akretschmer]

Zeig einfach mal ein Beispiel.

 

[alexfrenzel92]

Tabelle:
SpielerID | Gesamtstärke | Position | Priorität
1 | 76 | 1 | 1
5 | 99 | 5 | 2
4 | 63 | 4 | 2
2 | 80 | 4 | 2
3 | 89 | 2 | 2
5 | 65 | 3 | 1
6 | 41 | 4 | 2
9 | 35 | 5 | 2
3 | 69 | 1 | 2
2 | 42 | 4 | 2
10 | 41 | 3 | 2
5 | 56 | 3 | 2
8 | 88 | 2 | 1
6 | 44 | 4 | 2
5 | 34 | 1 | 2
1 | 55 | 5 | 2
2 | 31 | 2 | 2
4 | 57 | 4 | 2

Diese Tabelle soll sortiert werden nach Priorität asc, Gesamtstärke desc. In der fretigen Tabelle am Ende soll Position und SpielerID nur einmal in der jeweiligen Spalte vorkommen.

Die Sortierung sieht dann so aus: (Die Farbmarkierungen sind später relevant)
SpielerID | Gesamtstärke | Position | Priorität
8 | 88 | 2 | 1
1 | 76 | 1 | 1
5 | 65 | 3 | 1
5 | 99 | 5 | 2
3 | 89 | 2 | 2
2 | 80 | 4 | 2
3 | 69 | 1 | 2
...
9 | 35 | 5 | 2
...

Nun sollen noch die Zeilen gestrichen werden, die doppelt vorkommen:
Zeile 1 bleibt
Zeile 2 bleibt auch, weil Position und SpielerID nicht in der 1. Zeile stehen
Zeile 3 bleibt auch, weil Position und SpielerID in den Zeilen darüber stehen
Zeile 4 wird gestrichen, weil die Spieler ID in der dritten Zeile gleich ist
Zeile 5 wird auch gestrichen, weil die Position in Zeile 1 gleich ist
Zeile 6 bleibt
Zeile 7 wird gestrichen, weil die Position Zeile 2 gleicht
...
Die nächste Zeile der Tabelle ist 9 | 35 | 5 | 2.
Danach werden alle Zeilen gestrichen, weil alle Positionen verwendet wurden. Sollten alle SpielerIDs schon verwendet worden sein, schließt die Tabelle halt an dieser Stelle und manche Positionen treten gar nicht auf

Das Ergebnis wäre:
SpielerID | Gesamtstärke | Position | Priorität
8 | 88 | 2 | 1
1 | 76 | 1 | 1
5 | 65 | 3 | 1
2 | 80 | 4 | 2
9 | 35 | 5 | 2

 

[akretschmer]

Bist Du mit der letzten Zeile im Ergebnis Dir sicher?

test=*# select * from bla;
 sp_id | gs | pos | prio
-------+----+-----+------
  1 | 76 |  1 |  1
  5 | 99 |  5 |  2
  4 | 63 |  4 |  2
  2 | 80 |  4 |  2
  3 | 89 |  2 |  2
  5 | 65 |  3 |  1
  6 | 41 |  4 |  2
  9 | 35 |  5 |  2
  3 | 69 |  1 |  2
  2 | 42 |  4 |  2
  10 | 41 |  3 |  2
  5 | 56 |  3 |  2
  8 | 88 |  2 |  1
  6 | 44 |  4 |  2
  5 | 34 |  1 |  2
  1 | 55 |  5 |  2
  2 | 31 |  2 |  2
  4 | 57 |  4 |  2
(18 rows)

test=*# select * from (select *, row_number() over (partition by sp_id order by prio asc, gs desc) as sp_row, row_number() over (partition by pos order by prio asc, gs desc) as pos_row from bla order by prio asc, gs desc) bla where sp_row = 1 and pos_row = 1;
 sp_id | gs | pos | prio | sp_row | pos_row
-------+----+-----+------+--------+---------
  8 | 88 |  2 |  1 |  1 |  1
  1 | 76 |  1 |  1 |  1 |  1
  5 | 65 |  3 |  1 |  1 |  1
  2 | 80 |  4 |  2 |  1 |  1
(4 rows)

Zur Kontrolle mal noch das Zwischenresultat:

test=*# select * from (select *, row_number() over (partition by sp_id order by prio asc, gs desc) as sp_row, row_number() over (partition by pos order by prio asc, gs desc) as pos_row from bla order by prio asc, gs desc) bla ;
 sp_id | gs | pos | prio | sp_row | pos_row
-------+----+-----+------+--------+---------
  8 | 88 |  2 |  1 |  1 |  1
  1 | 76 |  1 |  1 |  1 |  1
  5 | 65 |  3 |  1 |  1 |  1
  5 | 99 |  5 |  2 |  2 |  1
  3 | 89 |  2 |  2 |  1 |  2
  2 | 80 |  4 |  2 |  1 |  1
  3 | 69 |  1 |  2 |  2 |  2
  4 | 63 |  4 |  2 |  1 |  2
  4 | 57 |  4 |  2 |  2 |  3
  5 | 56 |  3 |  2 |  3 |  2
  1 | 55 |  5 |  2 |  2 |  2
  6 | 44 |  4 |  2 |  1 |  4
  2 | 42 |  4 |  2 |  2 |  5
  10 | 41 |  3 |  2 |  1 |  3
  6 | 41 |  4 |  2 |  2 |  6
  9 | 35 |  5 |  2 |  1 |  3
  5 | 34 |  1 |  2 |  4 |  3
  2 | 31 |  2 |  2 |  3 |  3
(18 rows)

Ist allerdings wie bei mir üblich mit PostgreSQL gemacht, MySQL kann das nicht. Entweder bist jetzt traurig oder wechselst.

 

[alexfrenzel92]

Bist Du mit der letzten Zeile im Ergebnis Dir sicher?

Du meinst, weil die 5 schon verwendert wurde und deswegen raus müsste? Die 5 wurde in einer anderen Spalte verwendet, daher ist das kein Aussschlusskriterium, sondern nur, wenn sie in derselben Spalte vorkommt

Ist allerdings wie bei mir üblich mit PostgreSQL gemacht, MySQL kann das nicht. Entweder bist jetzt traurig oder wechselst.

Ich benutze MySQLi. Lohnt es sich da zu PostgreSQL zu wechseln?

 

[akretschmer]

Mmh, ich biete

test=*# select * from (select *,row_number() over (partition by pos order by prio asc, gs desc) as pos_row from (select *, row_number() over (partition by sp_id order by prio asc, gs desc) as sp_row from bla order by prio asc, gs desc) bla where sp_row = 1) xx where pos_row=1 order by prio, gs desc;
 sp_id | gs | pos | prio | sp_row | pos_row
-------+----+-----+------+--------+---------
  8 | 88 |  2 |  1 |  1 |  1
  1 | 76 |  1 |  1 |  1 |  1
  5 | 65 |  3 |  1 |  1 |  1
  2 | 80 |  4 |  2 |  1 |  1
  9 | 35 |  5 |  2 |  1 |  1
(5 rows)

Ob es sich für Dich lohnt mußt Du selber wissen ;-)