[MySQL /anySQL] Alphabetische plaats

[hobbit_be]

hoi, vraagje van iemand waar ik zelf geen antwoord op weet:

stel je hebt een adresboek en je wil weten wat de plek is van item met key "138". als deze table gesort zou worden op de achternaam.

het enige wat ik kon voorstellen is dat je als je een echte SQL zou hebben je een subselect zou kunnen doen? Maar dan had ik het probleem dat SQL dan een column zou moeten teruggeven met daarin de result-id (en hoe dat te doen weet ik tot mijn schande niet...)

Any simple solutions?

[ACM]

Ik snap je vraag niet echt? Wat wil je dat niet met een gewone (inner) join kan?

Kortom, leg het eens iets beter uit? Je bedoelt dat je alleen dat nummer 138 hebt, als je een rownumber zou meegeven en je de items op achternaam gesorteerd zou hebben?

Is het dan niet mogelijk een andere vorm van identificatie erin te verwerken?

Voor je rownum zou zoiets kunnen werken, zonder subselects evt:

SQL:

SELECT tabel.X, tabel.Y,
 count(inner.Y) as rownum
FROM tabel left join tabel inner ON inner.Y < tabel.Y
group by ...

Met een having kan je daar overigens nog direct de 138e uit halen, hoewel dat natuurlijk wel een vrij kostbare operatie is.

[ Voor 9% gewijzigd door ACM op 23-08-2004 16:12 ]

[klinz]

Je zou een count() kunnen doen op de items die voor dat specifieke item komen. De vraag is dan enkel nog wat je met niet-unieke achternamen doet.

code:

select count(achternaam) as plek
where achternaam <= "138"
order by achternaam

[hobbit_be]

betere uitleg sorry:

tabel A:

code:

primkey   naam  
147       Ann
138       Josef
114       David
8         Bella

ik wil nu weten dat wanneer is deze tabel sort op naam (ASC / DESC) ik wil weten op welke plaats in de result list (ie 1,2,3,4) het item staat met PRIM KEY 114 (David).

code:

resultindex  naam    id
1            Ann      147
2            Bella      8
3            david    114
4            Josef    138

114 -> 3 item ik wil 3 terug krijgen . Die inner join van ACM lijkt me wel iets te hebben hoewel ik die "inner.Y" veld niet goed snap. SQL is NOT my forte

[ Voor 11% gewijzigd door hobbit_be op 23-08-2004 17:09 ]

[djluc]

limit 137, 138 ofzo? Misschien moet het 136-137 zijn omdat ze bij 0 beginnen, maar dat moet je even uitzoeken.Niet helemaal goed gelezen.

[ Voor 84% gewijzigd door djluc op 23-08-2004 23:53 ]

[jvdmeer]

hobbit_be schreef op 23 augustus 2004 @ 17:08:
betere uitleg sorry:

tabel A:

code:

primkey   naam  
147       Ann
138       Josef
114       David
8         Bella

ik wil nu weten dat wanneer is deze tabel sort op naam (ASC / DESC) ik wil weten op welke plaats in de result list (ie 1,2,3,4) het item staat met PRIM KEY 114 (David).

114 -> 3 item ik wil 3 terug krijgen . Die inner join van ACM lijkt me wel iets te hebben hoewel ik die "inner.Y" veld niet goed snap. SQL is NOT my forte

Gokje, weet niet of het werkt, maar dit is de theoretische benadering:

SQL:

select count(*)+1 from tabelA where naam<(select naam from tabelA where ID=114)

edit:

Getest:
MS-SQL: geeft goede antwoord
MySQL: snapt er de ballen van

[ Voor 8% gewijzigd door jvdmeer op 23-08-2004 19:30 ]

[hobbit_be]

hmm een subvraagje dan:

hoe kan ik gewoon de resultid's krijgen in mijn query return?

ie -> mijn table a maar dan met een column result id... ? zelfs dat is me niet duidelijk?

[jvdmeer]

Helaas geldt hier ook weer:
MS-SQl voert dit probleemloos uit,
MySQL snapt er de ballen van.

SQL:

SELECT (SELECT COUNT(*) FROM tabelA a2 WHERE a2.naam <= a.naam) AS rownumber, naam, id
FROM tabelA a
ORDER BY naam

Maar zoek eens goed op google naar rownumber en mysql.

[ACM]

hobbit_be schreef op 23 augustus 2004 @ 17:08:
114 -> 3 item ik wil 3 terug krijgen . Die inner join van ACM lijkt me wel iets te hebben hoewel ik die "inner.Y" veld niet goed snap. SQL is NOT my forte

Als je goed las, had je gezien dat die inner een alias was die ik gebruikte voor de 2e keer dat ik de tabel gebruikte. Anyway, proof-of-concept:

code:

mysql> select * from adres;
+------+
| naam |
+------+
| acm  |
| bcm  |
| dcm  |
| ccm  |
+------+
4 rows in set (0.00 sec)

mysql> select a1.naam, count(a2.naam) as rownum from adres a1 left join adres a2 on
 a1.naam > a2.naam group by a1.naam;
+------+--------+
| naam | rownum |
+------+--------+
| acm  |      0 |
| bcm  |      1 |
| ccm  |      2 |
| dcm  |      3 |
+------+--------+
4 rows in set (0.02 sec)

mysql> select a1.naam, count(a2.naam) as rownum from adres a1 left join adres a2 on
 a1.naam > a2.naam group by a1.naam having rownum = 3;
+------+--------+
| naam | rownum |
+------+--------+
| dcm  |      3 |
+------+--------+
1 row in set (0.00 sec)

Met grote datasets kan dit trouwens efficienter zijn:

code:

mysql> create temporary table sorted_naam
  (rownum int auto_increment primary key, naam varchar(50)) type=HEAP;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sorted_naam (naam) select naam from adres order by naam;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from sorted_naam;
+--------+------+
| rownum | naam |
+--------+------+
|      1 | acm  |
|      2 | bcm  |
|      3 | ccm  |
|      4 | dcm  |
+--------+------+
4 rows in set (0.00 sec)

[ Voor 38% gewijzigd door ACM op 24-08-2004 23:00 ]

[hobbit_be]

ACM: die tweede optie snap ik -> beetje kwestie van geen subquery op te lossen in mySQL 3.x. Die =HEAP kende ik ook nog niet.

maar je eerste oplossing : wow. dit lijkt me een enorm zware query? en wat doe je als de naam niet uniek is? (eerst nog een concat op eg: naam + primkey -> zo is de volgorde steeds correct maar wordt het helemaal absurd zwaar).

jvdmeer: joue query is dus eigenlijk dezelfde als die van ACM maar dan wat leesbaarder (en natuurlijk mySQL 4.x only? + echt SQL).

ik heb eerste deftig gegoogled maar er bleek gewoon geen simpel solution: hoe simpel kan het nu zijn een :

code:

SELECT ROWNUMBER() as rownumber, ....

te bouwen???? dat lijkt me zo trivial dat ze het vergeten zijn.

dit kwam ik wel tegen:

code:

SELECT @n := @n+1 AS rownum, thing, arms, legs FROM limbs;

maar bleek problemen te hebben als er nog een conditie bij kwam. Heeft msSQL / ANSI sql niet iets voor de rownumber.

PS ik ben er wel van bewust dat de order niet teveel gebruikt mag / kan worden -> het probleem stelde zich de vraag in een adress boek dat als je een nieuwe entry maakte je daar ook een next / previous moet hebben (die dan naar de prev / vorige alphabetische entry moet gaan: ). Als je LIMIT wil gebruiken moet ik dus eerste de result row's vinden van de prev / next.