[MySQL] resultaten van group by sorteren op verborgen velden

[klokop]

stel 2 tabellen

code:

create table parent (p_id int(1))
insert into parent (p_id), (1), (2), (4)

create table child (c_id int(1), c_p_id int(1), c_nogiets int(1))
insert into child (c_id, c_p_id, c_nogiets) (1,1,80) ,
(2,1,60) ,(3,2,70) ,(4,2,30) ,(4,2,90)

Bij 'n

code:

select 
  p_id, 
  c_nogiets 
from parent 
inner join child on c_p_id = p_id 
group by c_p_id

zou je dan krijgen:

code:

1 | 80
2 | 70

Bij bovenstaande query is het maar afwachten welke waarde van chi_nogiets je te zien krijgt (ik neem aan de eerste waarde die de query 'tegenkomt', ik zou graag de laagste (danwel hoogste) waarde van 'c_nogiets' tonen.

Kan ik dan 'n "order by c_nogiets desc/asc' doen, zodat afhankelijk van de sortering, er verschillende lijsten met resultaten getoond worden?

Dus zoiets:
"... order by c_nogiets asc", met als resultaat:

code:

1 | 80
2 | 90

"... order by c_nogiets desc", met als resultaat:

code:

1 | 60
2 | 30

Het lijkt er op dat dit niet werkt bij moi (mysql 5.0). Hoe zou het WEL moeten?? Of zou dit moeten werken, en doe ik iets anders verkeerd?

[pedorus]

Je bedoelt iets als

SQL:

select 
  p_id, 
  c_nogiets 
from parent 
    inner join (select * from child order by c_nogiets desc) child 
    on c_p_id = p_id 
group by c_p_id

?

(NB: de code bij de 2 tabellen klopt niet helemaal)
Edit: de vraag was nog eenvoudiger dan ik dacht en het antwoord staat hieronder:

[ Voor 20% gewijzigd door pedorus op 04-06-2008 23:22 ]

[_js_]

Kijk hier eens: http://dev.mysql.com/doc/...n/group-by-functions.html
Daar staat precies wat jij wilt.

[klokop]

Ah, ok, max() en/of min() dus.

code:

select 
  p_id, 
  max(c_nogiets)
from parent 
inner join child on c_p_id = p_id 
group by c_p_id

Thx.

[Alain]

order by is bedoeld om je eindresultaat in de juiste volgorde te zetten voor presentatie. Volgens het relationele model heeft dit geen enkele waarde. De oplossing (een statistische functie) is al gegeven.

Een oplossing zoal pedorus geeft zou ik zelf nooit gebruiken. Een order by hoort imho nooit in een subquery thuis, omdat het alleen invloed heeft op de presentatie.

offtopic:
Het zou fijn zijn als er voor een serieuze case gekozen wordt om een probleem voor te leggen, i.p.v. een virtueel voorbeeld waarbij de kolomnamen nergens op slaan. Een kolomnaam hoort semantisch te zijn zodat de buitenwereld ook kan zien wat je ermee bedoeld en wilt bereiken.

[Redshark]

Een oplossing zoal pedorus geeft zou ik zelf nooit gebruiken. Een order by hoort imho nooit in een subquery thuis, omdat het alleen invloed heeft op de presentatie.

Volgens mij staan veel rdbms-en het ook niet toe (iig ms sql niet, tenzij je de resultaten limiteerd. MySQL is daar toch vaak wat soepeler mee

[ Voor 4% gewijzigd door Redshark op 04-06-2008 23:45 ]

[pedorus]

Die oplossing met order by werkt in combinatie met FIRST() ook op veel andere RDBMS-en. Hier is het natuurlijk een 'foute' oplossing, MIN() of MAX() zijn er voor gemaakt.

Ik was even in verwarring gebracht door de titel ('verborgen velden'). Wel of niet gebruiken is soms het verschil tussen wel of geen snel resultaat.

[Alain]

Redshark schreef op woensdag 04 juni 2008 @ 23:45:
[...]

iig ms sql niet, tenzij je de resultaten limiteerd

Ik vind het persoonlijk een rare situatie om het wel toe te staan als de result set gelimiteerd wordt. Weet je wat de reden hiervan is?

[DizzyWeb]

AlainS schreef op woensdag 04 juni 2008 @ 23:58:
[...]


Ik vind het persoonlijk een rare situatie om het wel toe te staan als de result set gelimiteerd wordt. Weet je wat de reden hiervan is?

Omdat het dan weldegelijk waarde heeft? Enkel order by is alleen cosmetisch, volgorde veranderen. Maar met een limit erbij kan je "de 10 hoogste waarden" selecteren. En dat is meer dan enkel volgorde bepalen.

[Redshark]

Ik vind het net zo raar maar waarom het zo is heb ik eigenlijk nooit uitgezocht. Een google leverde wel dit stukje op:
Behavior of the ORDER BY clause in views, derived tables, inline functions, and subqueries in SQL 2000

Edit: voorganger is scherper

[ Voor 6% gewijzigd door Redshark op 05-06-2008 00:10 ]

[Alain]

DizzyWeb schreef op donderdag 05 juni 2008 @ 00:08:
[...]

Omdat het dan weldegelijk waarde heeft? Enkel order by is alleen cosmetisch, volgorde veranderen. Maar met een limit erbij kan je "de 10 hoogste waarden" selecteren. En dat is meer dan enkel volgorde bepalen.

Daar had ik inderdaad niet aan gedacht.

[pedorus]

Redshark schreef op donderdag 05 juni 2008 @ 00:09:
Behavior of the ORDER BY clause in views, derived tables, inline functions, and subqueries in SQL 2000

Een heel artikel over dit onderwerp, terwijl ze niet de workaround met "top 100 percent" geven... (Het blijven natuurlijk een beetje ranzige trucs, maar soms wil je iets dat werkt.)

[klokop]

Kan het zijn dat mijn 'oude' methode (dus met een order by op velden die uiteindelijk helemaal niet getoond worden.) in mysql < 5 wel werkte? Kan me herinneren dat ik het ooit wel 'ns zo gedaan heb namelijk.

[Verwijderd]

DizzyWeb schreef op donderdag 05 juni 2008 @ 00:08:
Omdat het dan weldegelijk waarde heeft? Enkel order by is alleen cosmetisch, volgorde veranderen. Maar met een limit erbij kan je "de 10 hoogste waarden" selecteren. En dat is meer dan enkel volgorde bepalen.

Sterker nog, LIMIT is niets anders dan een select clause:

code:

select * from (
  select * from my_table order by my_table.name
) where row_number between(1, 10)

[Voutloos]

klokop schreef op donderdag 05 juni 2008 @ 00:38:
Kan het zijn dat mijn 'oude' methode (dus met een order by op velden die uiteindelijk helemaal niet getoond worden.) in mysql < 5 wel werkte? Kan me herinneren dat ik het ooit wel 'ns zo gedaan heb namelijk.

Nee, zoals je met:

klokop schreef op woensdag 04 juni 2008 @ 22:36:
Bij bovenstaande query is het maar afwachten welke waarde van chi_nogiets je te zien krijgt (ik neem aan de eerste waarde die de query 'tegenkomt',

zelf al een beetje aanvoelt is het een kansloze vraag om random data. Het is ook niet altijd de 1e waarde zoals je die zou verwachten. Dat het -bij bepaalde queries/streekproeven- eerst wel leek te werken berust puur op toeval. Order by zegt niets over de volgorde van rijen op het moment van groeperen, of over het oplossen van dergelijke conflicterende waarden.

Dus: selecteer enkel velden welke in de group by zijn, of welke het resultaat zijn van een aggregate function ('group by function'). En dat is regel numero uno met het gebruiken van group by, maar helaas vanwege het default te aardige gedrag van mysql een te slecht bekende regel.