zweit- und dritthöchsten Wert einer Tabelle ausgeben

[Tobi1982]

Ich habe folgendes Problem.

Ich habe eine Tabelle mit folgenden Spalten id, user, anzahl_aktualisierungen

Beispiel:

1, Achim, 10
2, Bernd, 7
3, Chris, 7
4, Dennis, 4
5. Egon, 2

Ich möchte mit einer Abfrage den zweithöchsten Wert erhalten, also in dem Fall die 7. Es ist egal wie oft der Wert vorkommt. Es soll nur der zweithöchste Wert sein.

Das gleiche für den dritthöchsten Wert, also 4.

Den höchsten Wert habe ich bereits mit folgender Abfrage ermitteln können:

$result = mysql_query("SELECT MAX(anzahl_aktualisierungen) AS anzahl_aktualisierungen FROM TABELLE");
$row = mysql_fetch_array($result);
$max_anzahl_aktualisierungen = $row['anzahl_aktualisierungen'];

Bekomme die anderen beiden nicht hin. Habe es schon mit sortieren und LIMIT usw. probiert.

Ich will eine Art Platzierung machen, also Gold, Silber, Bronze. Achim wäre gold, Bernd und Chris silber und Dennis bronze. Egon wäre aus der Wertung, weil er nicht unter den TOP 3 ist.

 

[Kampfgummibaerlie]

"egal wie oft was vorkommt" ist mit der distinct-funktion machbar.

nur würde ich gerne wissen (Hintergrund), warum du den dritthöchsten wert oder etc brauchst...

Mein Lösungsangang:

create table tabelle(id serial primary key, value integer);
insert into tabelle(value) values (10), (7), (7), (4), (2);
select distinct(value) from tabelle;

Den zweithöchsten wert bekomme ich, indem ich in einer where-bedingung schreibe, dass der wert klener sein soll als max()...

select distinct(value) from tabelle where value < (select max(value) from tabelle) order by value desc limit 1;

 

[akretschmer]

geht noch einfacher:

postgres=# select distinct value from tabelle order by value desc limit 1 offset 1;
 value 
-------
     7
(1 row)

postgres=# select distinct value from tabelle order by value desc limit 1 offset 2;
 value 
-------
     4
(1 row)

postgres=#

 

[akretschmer]

crosspost mit zweit- und dritthöchsten Wert einer Tabelle ausgeben - php.de

Aber ich will mal nicht so sein, wenn es eine Plazierung werden soll suchst Du rank():

postgres=# select *, rank() over (order by value) from tabelle;
 id | value | rank 
----+-------+------
  5 |     2 |    1
  4 |     4 |    2
  2 |     7 |    3
  3 |     7 |    3
  1 |    10 |    5
(5 rows)

 

[Tobi1982]

Danke für die Hinweise ich habe es jetzt anders gelöst - mit DESC und LIMIT. Etwas viele Abfragen aber es funktioniert.
Ich muss soviele if Abfragen machen, ansonsten bekomme ich Fehler, weil ich auch die User nach Buchstaben auflisten kann. Gibt es nur einen User mit dem Anfangsbuchstaben A, dann ist er automatisch gold. Es gab aber Fehlermeldungen, weil es keine silber und bronze User mit A als Anfangsbuchstaben gibt. Weil immer 0 oder leer zurückgegegen wurde. Jetzt frage ich aber vorher ab, ob es immer den nächsten Platz überhaupt gibt.

##### Gold - Anfang ###### if($buchstabensuche != "alle") { $result = mysql_query("SELECT MAX(anzahl_aktualisierungen) AS anzahl_aktualisierungen FROM gta WHERE freund LIKE '$buchstabensuche%'"); $count = mysql_num_rows($result); if($count > 0) { $row = mysql_fetch_array($result); $max_anzahl_gold = $row['anzahl_aktualisierungen']; } if($max_anzahl_gold > 0) { $result = mysql_query("SELECT COUNT(anzahl_aktualisierungen) AS gold FROM gta WHERE freund LIKE '$buchstabensuche%' AND anzahl_aktualisierungen = '$max_anzahl_gold'"); $row = mysql_fetch_array($result); $gold = $row['gold']; } else { $max_anzahl_gold = 0; $gold = 0; } } ##### Gold - Ende ###### ##### Silber - Anfang ###### if($buchstabensuche != "alle") { $result = mysql_query("SELECT anzahl_aktualisierungen FROM gta WHERE freund LIKE '$buchstabensuche%' AND anzahl_aktualisierungen < $max_anzahl_gold ORDER BY anzahl_aktualisierungen DESC LIMIT 1"); $count = mysql_num_rows($result); if($count > 0) { $row = mysql_fetch_array($result); $max_anzahl_silber = $row['anzahl_aktualisierungen']; } if($max_anzahl_silber > 0) { $result = mysql_query("SELECT COUNT(anzahl_aktualisierungen) AS silber FROM gta WHERE freund LIKE '$buchstabensuche%' AND anzahl_aktualisierungen = '$max_anzahl_silber'"); $row = mysql_fetch_array($result); $silber = $row['silber']; } else { $max_anzahl_silber = 0; $silber = 0; } } ##### Silber - Ende ###### ##### Bronze - Anfang ###### if($buchstabensuche != "alle") { $result = mysql_query("SELECT anzahl_aktualisierungen FROM gta WHERE freund LIKE '$buchstabensuche%' AND anzahl_aktualisierungen < $max_anzahl_silber ORDER BY anzahl_aktualisierungen DESC LIMIT 1"); $count = mysql_num_rows($result); if($count > 0) { $row = mysql_fetch_array($result); $max_anzahl_bronze = $row['anzahl_aktualisierungen']; } else { $max_anzahl_bronze = 0; $bronze = 0; } if($max_anzahl_bronze > 0) { $result = mysql_query("SELECT COUNT(anzahl_aktualisierungen) AS bronze FROM gta WHERE freund LIKE '$buchstabensuche%' AND anzahl_aktualisierungen = '$max_anzahl_bronze'"); $row = mysql_fetch_array($result); $bronze = $row['bronze']; } else { $max_anzahl_bronze = 0; $bronze = 0; } } ##### Bronze - Ende ######

 

[Tobi1982]

Hab aber noch ne andere Frage.

Wie muss die Abfrage lauten, wenn ich alle User ermitteln möchte, die mit einer Zahl als Username anfangen. Als mysql Abfrage. Nicht erst alle auslesen und dann überprüfen is numeric. Einen Schritt schneller, gleich nur die auslesen die eine 0 - 9 als erstes Zeichen haben.

 

[dabadepdu]

By mysql glaub ich so..
.. where meineSpalte regexp '^[0-9]+'

Und noch dazu:

##### Gold - Anfang ######

Dir ist klar, dass Du mehrfach die DB abfragst, obwohl es sehr wahrscheinlich ist, das eine Abfrage reicht?
Damit hast Du grob überschlagen folgendes Ressourcenproblem:
1 Abfrage = Faktor 1 (ideal)
3 Abfragen (statt einer) = Faktor 3
usw.
Das ist erstmal unauffällig, aber läuft Dir in einem Moment in die Hacken, wenn Du es nicht brauchen kannst. An dem Punkt, wo Dein System in Schwung kommt, weil es gut genutzt wird. Dann musst Du zwangsläufig optimieren. Also bessere Codierung (3 statt 1 Abfrage)/ mehr Hardware/ Loadbalancer / ..

 

[tk1234]

Neben dem was dabadepdu schon schrieb (einfach nur kopierter und leicht abgeänderter Code ist idR ein Hinweis auf schlechten Code und gehört in eine Funktion ausgelagert - wobei sich das ganze hier ja vmtl. auch mit einem einzigen Query erschlagen lässt):

$result = mysql_query("SELECT COUNT(anzahl_aktualisierungen) AS gold FROM gta WHERE freund LIKE '$buchstabensuche%' AND anzahl_aktualisierungen = '$max_anzahl_gold'");

Hier fehlt die Behandlung des Kontextwechsels, zudem gibt es die Funktion mysql_query() seit 4 Jahren nicht mehr. Und nein, irgendwelche Scripte die die mysql_*-Funktionen wieder nachrüsten sind keine Lösung sondern Pfusch der höchstens als provisorische Lösung taugt aber ganz sicher nicht für neue Scripte, da würde ich empfehlen PDO zu verwenden.

 

[akretschmer]

Naja, rank() zeigte ich ja schon, könnte man noch erweitern auf den PARTITION BY NUTZERNAME oder so. Aber hey, warum einfach, wenn es auch kompliziert & fehleranfällig geht, gell?

 

[Tobi1982]

Vielen Dank für die Hinweise. Ich bin schon seit zig Jahren raus aus der Programmierung. Des Weiteren läuft nur ein altes PHP (5.2.17) aufn evanzo server. Aber solange meine Scripte für mich laufen ist mir das egal. Ich müsste sonst zig Scripte umschreiben.

Habe mein letztes Problem folgendermaßen gelöst:

$result = mysql_query("SELECT * FROM tabelle WHERE user REGEXP '^[0-9]+'");

So findet man jeden Datensatz der mit einer Zahl beginnt. Was danach folgt ist egal.

 

[akretschmer]

Das klingt, als ob Du ein Museum betreibst...

Nachtrag: ich hoffe für Dich, daß das nicht aus dem Internet erreichbar ist. Könnte teuer werden. Aber Dein 'läuft auf einem evanzo server' läßt mich vermuten, daß dem nicht so ist.