[MySQL] Positie row binnen result list

[Verwijderd]

Graag zou ik de positie van een bepaalde row, zeg met bijvoorbeeld id 3, binnen een result set van een query achterhalen.

Dus stel:

code:

ID   Name
1    Christiaan
2    Dolf
3    Bart
4    Aart

Wordt geordend op naam:

code:

SELECT * FROM users ORDER BY `name`

code:

ID   Name
4    Aart
3    Bart
1    Christiaan
2    Dolf

Hoe kan ik nu achterhalen welke positie Bart heeft? (2 in dit geval)
Het gaat om een grote database dus eerst alles ophalen en dan met PHP achterhalen lijkt me geen optie.

[remco_k]

Ik zou daar zo snel niet in een query een oplossing voor weten.
Mag ik vragen waarom je dit wilt?

[Verwijderd]

Tellertje bijhouden?

[sql]
SET @row = 0;

SELECT *, @row := @row + 1 AS Row
FROM users order by name;
[sql]

[ Voor 8% gewijzigd door Verwijderd op 19-09-2007 15:04 ]

[remco_k]

Verwijderd schreef op woensdag 19 september 2007 @ 13:46:
Tellertje bijhouden?

code:

SET @row = 0;

SELECT *, @row := @row + 1 AS Row
FROM users order by name;

Is niet echt zinvol, moet hij nog steeds client side (in dit geval met PHP) door de resultset heen itereren. Kan hij net zo goed zelf even meetellen.

[Verwijderd]

Ik heb een tabel met daarin een aantal scores en namen.
Nu wil ik dat als iemand zijn naam opgeeft hij teruggeeft welke plek hij heeft in de ranglijst.

[torx]

Kan je niet gewoon de score ophalen, en dan een query doen die het aantal scores hoger dan de betreffende score telt?

bv. SELECT COUNT(*) FROM scoretabel WHERE score>575

[ Voor 3% gewijzigd door torx op 19-09-2007 14:30 . Reden: typfoutjes ]

[Grijze Vos]

Verwijderd schreef op woensdag 19 september 2007 @ 14:27:
Ik heb een tabel met daarin een aantal scores en namen.
Nu wil ik dat als iemand zijn naam opgeeft hij teruggeeft welke plek hij heeft in de ranglijst.

Dus je wilt het aantal mensen met een betere score + 1...

SQL:

SELECT COUNT(*) AS rank
FROM scoretabel WHERE 
score > 
  SELECT score
  FROM scoretabel
  WHERE id = 3;

result van die query + 1 is je antwoord.

[ Voor 31% gewijzigd door Grijze Vos op 20-09-2007 12:29 ]

[Crayne]

Verwijderd schreef op woensdag 19 september 2007 @ 14:27:
Ik heb een tabel met daarin een aantal scores en namen.
Nu wil ik dat als iemand zijn naam opgeeft hij teruggeeft welke plek hij heeft in de ranglijst.

Dan wil je dus niet ORDER BY 'name' doen maar ORDER BY 'score' DESC en vervolgens in de verwerking van je resultset inderdaad een tellertje mee laten lopen. Waarom wil je dat per sé in een sql query doen als je de data toch nog moet bewerken voor presentatie aan je gebruiker?

Edit:

Wat is er mis met iets simpels als dit in PHP:

PHP:

<?php

$userName = $_POST['user'];

$sql = "SELECT * FROM users ORDER BY score DESC";

$result = mysql_query($sql);

$counter = 0;

while ($row = mysql_fetch_assoc($result) {

    $counter++;
        
    if ($row['name'] == $userName) {
        
        break;
        
    }
    
}

echo "Je staat op de ".$counter."e plaats in het klassement.";

?>

[ Voor 27% gewijzigd door Crayne op 19-09-2007 14:53 ]

[Mr_Groovy]

Makkelijke weg: Insert een extra field in je tabel 'autoID' die geeft je de rij weer.
Php weg:
Maak eerst een array aan met ID, daarna sorteren op naam. Heeft wel als nadeel dat je 2x de database binnenhaald en dat het via php gedaan wordt.

[Grijze Vos]

Crayne schreef op woensdag 19 september 2007 @ 14:33:
[...]
Wat is er mis met iets simpels als dit in PHP:

Los ervan dat jij 10 regels code nodig hebt voor iets wat met 1 SQL query in een keer gereturned kan worden. Stel je eens voor dat je 100.000 gebruikers hebt, en dat voor elke gebruiker het record pak em beet 10 bytes is, dan verstuur jij dus eventjes 1.000.000 bytes (= 1 GMB!) van database naar je PHP applicatie, terwijl je een getal van slechts 2 bytes terug wilt returnen.

Mr_Groovy schreef op woensdag 19 september 2007 @ 14:42:
Makkelijke weg: Insert een extra field in je tabel 'autoID' die geeft je de rij weer.

Superfluous info in je database = bad. Daarnaast, is dit uberhaupt wel mogelijk? Een autoID in de normale zin van het woord loopt op als een user wordt toegevoegd, de TS heeft het over een score, die neem ik aan kan veranderen met de tijd.

Php weg:
Maak eerst een array aan met ID, daarna sorteren op naam. Heeft wel als nadeel dat je 2x de database binnenhaald en dat het via php gedaan wordt.

Zie mijn response op Crayne.

Serieus, dit soort code voorbeelden en ideeen verwacht ik tegen te komen op worsethanfailure.com, niet op tweakers.

[ Voor 9% gewijzigd door Grijze Vos op 20-09-2007 14:24 ]

[Verwijderd]

Zo dan:

SQL:

SET @row = 0;

SELECT Row FROM 
(SELECT 
*, @row := @row + 1 AS Row
FROM users order by name) ranking
WHERE ranking.name = "Bart"

[ Voor 3% gewijzigd door Verwijderd op 19-09-2007 15:12 ]

[Mr_Groovy]

Grijze Vos schreef op woensdag 19 september 2007 @ 15:08:

Serieus, dit soort code voorbeelden en ideeen verwacht ik tegen te komen op worsethanfailure.com, niet op tweakers.

Misschien had je database in de eerste instantie slimmer moeten op zetten

[Grijze Vos]

Mr_Groovy schreef op donderdag 20 september 2007 @ 12:10:
[...]


Misschien had je database in de eerste instantie slimmer moeten op zetten

Wat is dat voor een onzin comment. De TS heeft een database structuur waarin scores worden bijgehouden en hij wil de ranking weten bij die score. Wat is daar mis mee? Ga jij bij elke user+score die toegevoegd wordt eventjes de ranking opnieuw uitrekenen voor de hele table en dat even updaten?

Daarbij, omdat hij -in jouw ogen- een suboptimale database structuur is raad jij hem een brakke implementatie aan, terwijl het ook beter kan.

edit:

Zie btw net dat Crayne's voorbeeld code ook nog eens een verkeerde result teruggeeft als de naam niet voorkomt in de DB.

[ Voor 19% gewijzigd door Grijze Vos op 20-09-2007 12:30 ]

[Icelus]

Grijze Vos schreef op woensdag 19 september 2007 @ 15:08:
[...]

Stel je eens voor dat je 100.000 gebruikers hebt, en dat voor elke gebruiker het record pak em beet 10 bytes is, dan verstuur jij dus eventjes 1.000.000 bytes (= 1 GB!) van database naar je PHP applicatie, terwijl je een getal van slechts 2 bytes terug wilt returnen.

Volgens mij een factor 1.000 minder, maar nog steeds te veel

Probeer dit 'ns:

SQL:

  SELECT COUNT(*) AS rij, a.id, a.naam
    FROM tabel AS a, tabel AS b
   WHERE a.naam >= b.naam
GROUP BY a.id, a.naam
ORDER BY a.naam ASC

en direct een naam selecteren:

SQL:

  SELECT COUNT(*) AS rij, a.id, a.naam
    FROM tabel AS a, tabel AS b
   WHERE a.id = 3
     AND a.naam >= b.naam
GROUP BY a.id, a.naam
ORDER BY a.naam ASC

[Spiral]

offtopic:
Grijze Vos schreef op woensdag 19 september 2007 @ 15:08:

[quote]Los ervan dat jij 10 regels code nodig hebt voor iets wat met 1 SQL query in een keer gereturned kan worden. Stel je eens voor dat je 100.000 gebruikers hebt, en dat voor elke gebruiker het record pak em beet 10 bytes is, dan verstuur jij dus eventjes 1.000.000 bytes (= 1 GB!) van database naar je PHP applicatie, terwijl je een getal van slechts 2 bytes terug wilt returnen.
[/quote]

1.000.000 Bytes is nog altijd één MegaByte

[ Voor 0% gewijzigd door Spiral op 20-09-2007 13:00 . Reden: Verdorie te laat! ]

[Grijze Vos]

Spiral schreef op donderdag 20 september 2007 @ 12:59:

1.000.000 Bytes is nog altijd één MegaByte

I stand corrected.
Tis nog altijd een factor 1 miljoen teveel..

Wil jij elke request een paar MB door je server heen raggen omdat er een paar queries slecht geschreven zijn?