[MySQL] order by werkt niet

[Noxious]

Zie onderstaande code, dit zou gesorteerd moeten zijn op datum/tijd maar dat werkt dus niet.
Heeft iemand een idee waarom?

code:

mysql> select profielvisits.visited,profielvisits.visitor,MAX(profielvisits.when) from profielvisits WHERE visited = 92831 group by visitor order by profielvisits.when DESC;
+---------+---------+-------------------------+
| visited | visitor | MAX(profielvisits.when) |
+---------+---------+-------------------------+
|   92831 |   92889 | 2009-04-25 12:32:56     |
|   92831 |   92881 | 2009-04-24 08:32:53     |
|   92831 |   92879 | 2009-04-10 19:37:51     |
|   92831 |   92880 | 2009-04-10 09:22:34     |
|   92831 |   92877 | 2009-04-06 21:00:04     |
|   92831 |   92842 | 2009-04-04 14:42:58     |
|   92831 |   92874 | 2009-04-06 20:53:45     |
|   92831 |   92834 | 2009-04-25 12:22:16     |
+---------+---------+-------------------------+
8 rows in set (0.00 sec)

code:

mysql> describe profielvisits;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| ID      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| visited | bigint(20) unsigned | NO   |     | NULL    |                |
| visitor | bigint(20) unsigned | NO   |     | NULL    |                |
| when    | datetime            | NO   |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Ik heb al gegoogled en wel wat andere mensen met dit probleem gezien, maar nergens een oplossing.

Kan iemand me een schop in de juiste richting geven?

Toevoeging: als ik ASC doe, dan is de rij wel andersom dus er wordt wel 'iets' gesorteerd.

[momania]

Noxious schreef op zaterdag 25 april 2009 @ 12:47:

Kan iemand me een schop in de juiste richting geven?

code:

MAX(profielvisits.when) <> profielvisits.when

[Noxious]

momania schreef op zaterdag 25 april 2009 @ 12:50:
[...]

code:

MAX(profielvisits.when) <> profielvisits.when

gretverdrie het werkt nog ook.

Wel raar.. of doet de order dan op de eerste verschijning ipv de max? Dat zou verklaren waarom 92834 bovenaan blijft staan, dit was de eerste testuser na mij (92831).

Tnx het is fixed

[ACM]

Noxious schreef op zaterdag 25 april 2009 @ 12:52:
gretverdrie het werkt nog ook.

Wel raar.. of doet de order dan op de eerste verschijning ipv de max? Dat zou verklaren waarom 92834 bovenaan blijft staan, dit was de eerste testuser na mij (92831).

Je hebt zijn opmerking toch niet als serieus onderdeel van je query geinterpreteerd he?? Wat hij je probeerde duidelijk te maken is dat de MAX() van dat veld iets anders is dan sorteren op de losse waarden in de MAX.

Het netst is om zoiets met je query te doen, een alias toevoegen en daarop sorteren:

SQL:

SELECT ..., MAX(veld) as maxveld FROM ... ORDER BY maxveld

[SPee]

Het is heel nasty:

SQL:

 select visited, visitor, MAX(profielvisits.when) 
 from profielvisits
 WHERE visited = 92831
 GROUP BY visitor 
 ORDER BY profielvisits.when DESC;

Er wordt eerst de waardes uit tabel profielvisits gehaald, die bij visited de waarde 92831 hebben.
Daarna wordt die gegroepeerd per visitor en vervolgens gesorteerd. Daarna worden de rijen met de maximale waarde bij profielvisits.when getoond. Als je 'MAX' weghaalt in de SELECT zie je welke volgorde de records zitten (op tijdvolgorde bij 'when'). Als je dan de 'niet MAX' wegstreept uit die records, krijg je jouw lijstje.

Dit is te veranderen door de MAX ook in de ORDER BY te gebruiken:

SQL:

 select visited, visitor, MAX(profielvisits.when) 
 from profielvisits
 WHERE visited = 92831
 GROUP BY visitor 
 ORDER BY MAX(profielvisits.when) DESC;

Dat moet resulteren in de nette code van ACM hierboven.

[ Voor 1% gewijzigd door SPee op 25-04-2009 14:52 . Reden: code tags ]

[Noxious]

@ SPee: dat had ik dus gedaan ja
uiteraard kan ik ook sorteren met een as zoals ACM doet, of gewoon MAX in de order by stoppen. Zelfde effect lijkt me zo.

Toevoeging: mijn query ziet er nu dus zo uit:

SQL:

SELECT profielvisits.visited,profielvisits.visitor,MAX(profielvisits.whenvis) AS whenvis
FROM profielvisits
WHERE visited = <hier de userid>
GROUP BY visitor
ORDER BY MAX(profielvisits.whenvis) DESC
LIMIT 50;

En ja, when is renamed naar whenvis when gaf wat probleempjes aangezien MySQL dit als een command ziet.

Edit: zou ORDER BY whenvis in theorie sneller zijn omdat er minder data in dat veld staat?

[ Voor 81% gewijzigd door Noxious op 25-04-2009 16:36 ]

[user109731]

Noxious schreef op zaterdag 25 april 2009 @ 16:29:
when gaf wat probleempjes aangezien MySQL dit als een command ziet.

Kun je oplossen door `when` te gebruiken, maar ik vind het zelf idd netter om geen keywords te gebruiken.

Edit: zou ORDER BY whenvis in theorie sneller zijn omdat er minder data in dat veld staat?

Het is in theorie sneller omdat je maar 1 keer de MAX berekent, maar ik weet niet hoe MySQL intern hiermee omgaat: wellicht maakt het niets uit of herkent de optimizer het

Ik vind het sowieso mooier/netter om whenvis te gebruiken, zodat de MAX maar op 1 plaats staat.

[ Voor 18% gewijzigd door user109731 op 25-04-2009 17:38 ]

[RobertMe]

Het is je bekend dat deze query alleen door "pure toeval" juiste resultaten geeft, en dat iedere andere db-server, of een goed geconfigureerde MySQL server, deze query meteen afschiet met een error?

Je doet een GROUP BY op visitor, dat betekend dus dat je alleen visitor kan selecteren, of een aggregate functie (zoals MAX), maar, je selecteert ook visited, dit kan dus helemaal niet, MySQL voert, om wat voor rede dan ook, deze query wel uit, maar het resultaat is onzin, omdat ie bij de GROUP BY visitor, een willekeurig record uit het groepje pakt, en daarbij visited ophaalt. Nu zal dit wel altijd het goede/zelfde resultaat zijn, omdat je WHERE visited = ... doet.
Je moet dus of visited toevoegen aan de GROUP BY (maakt voor het resultaat volgensmij niet uit), of je haalt visited helemaal weg uit de select, omdat je toch weet wat je ophaald, want die geef je ook mee bij de WHERE.

Zie ook deze link, met wat meer uitleg over GROUP BY en de rare kronkels van MySQL (en met een link naar uitleg hoe je MySQL goed kan configureren)

[Noxious]

Je wilt dus zeggen dat MySQL met een standaardconfiguratie niet goed geconfigureerd is?

Mij lijkt de query zoals 'ie nu is logisch, waarom kan je alleen het veld selecteren waarop je groupt?
Volgens mij wordt in de link die je geeft in het autovoorbeeld namelijk exact hetzelfde gedaan.

Edit: Hmm ik zie de 'fout' bij het voorbeeld, echter is dat in mijn geval niet heel erg relevant, aangezien de datum het enigste is wat wijzigt.

Overigens heb ik 'm query nu als volgt:

SQL:

SELECT visitor,MAX(whenvis) AS whenvis 
FROM profielvisits 
WHERE visited = <hier de userid> 
GROUP BY visitor 
ORDER BY whenvis DESC 
LIMIT 50;

[ Voor 43% gewijzigd door Noxious op 27-04-2009 14:11 ]

[RobertMe]

Je wilt dus zeggen dat MySQL met een standaardconfiguratie niet goed geconfigureerd is?

Klopt

Mij lijkt de query zoals 'ie nu is logisch, waarom kan je alleen het veld selecteren waarop je groupt?

Wat moet ie dan selecteren? Als ik een groep heb waarin 100 items zitten, dan kan ie niet daaruit zomaar een item halen en dat gebruiken voor de dingen waarop je geen GROUP BY doet.

Volgens mij wordt in de link die je geeft in het autovoorbeeld namelijk exact hetzelfde gedaan.

Maar dat hoeft toch niet altijd?

dat stukje moet je even lezen.

Merk	Type	MAX(huurprijs)
Opel	Astra	345
Audi	A4	500
BMW	Z3	1000

De Opel Astra kost 200, de Audi A4 kost 300, van BMW word er maar eentje genoemd, de Z3 met prijs 1000, dus die klopt wel, omdat er maar een item in de groep BMW zit.

[Dido]

Een vraagje tussendoor: waarom gebruik je in je select clause je tabelnaam-prefixes, terwijl dat niet nodig is?

Als je het nou consequent zou doen, maar in je where en je group by clauses doe je het weer niet.

Je selecteert maar uit 1 tabel, dus hoef je niet aan te geven uit welke tabel de velden in je select komen. De leesbaarheid gaat er niet op vooruit als je volledige tabelnamen herhaalt, namelijk.

[RobertMe]

Hmm ik zie de 'fout' bij het voorbeeld, echter is dat in mijn geval niet heel erg relevant, aangezien de datum het enigste is wat wijzigt.

De query is dan nog steeds fout Zoals ik zelf al aangaf, door toeval (WHERE visited) zal het resultaat altijd correct zijn, maar de query kan/mag niet.

[Janoz]

Noxious schreef op maandag 27 april 2009 @ 13:51:
Je wilt dus zeggen dat MySQL met een standaardconfiguratie niet goed geconfigureerd is?

Klopt

Mij lijkt de query zoals 'ie nu is logisch, waarom kan je alleen het veld selecteren waarop je groupt?

Het punt is dat een aggregerende functie als MAX een verzameling waarden terug brengt tot 1. Dat lijkt misschien vreemd omdat je het idee kunt hebben dat het resultaat van de MAX de waarden van 1 van de records is en er dus een record bij hoort, maar dat is dus niet het geval. Het is wat makkelijker voor te stellen wanneer je kijkt naar andere aggrgerende functies als SUM, COUNT em AVG. Daarbij is het veel duidelijker te zien dat het resultaat van dergelijke functies niet 1 record is, maar een wiskundige bewerking op alle resultaten, en daarom eigenlijk ook bij alle records horen.

Ander voorbeeld: Welk record zou je terug willen hebben wanneer de maximale waarde 2x voorkomt?

[Noxious]

Hmm ik vat 'm, ik zie echter nog niet in wat ik direct kan verbeteren. GROUP BY visitor,whenvis?
Wat gebeurt er dan als er 2 verschilende visitors op dezelfde tijd langskwamen?
Iets met MAX doen voordat de GROUP BY wordt gedaan?

@Dido: dit had ik eerst niet, maar 'when' zag MySQL als een commando ipv de veldnaam daarom had ik dit toegevoegd, kan nu in principe weer weg aangezien ik dat veld heb gerenamed.

Ander voorbeeld: Welk record zou je terug willen hebben wanneer de maximale waarde 2x voorkomt?

Hmm dan zou iemand in 1 seconde tijd 2x hetzelfde profiel bezocht moeten hebben. In theorie kan dat, maar zal weinig voorkomen en de rest van de waardes in de tabel zijn hetzelfde, maar i got your point

[ Voor 27% gewijzigd door Noxious op 27-04-2009 14:13 ]

[RobertMe]

GROUP BY visitor,whenvis

Dat hoeft niet, omdat MAX op de GROUP BY werkt, dat mag dus wel (MAX selecteerd de maximum waarde van de gegeven kolom, in dat groepje).

edit:
Hoeft niet, als in, je kunt gewoon GROUP BY visitor doen

Voorbeeld van phpfreakz:
Selecteer de maximale huurprijs per automerk, dus GROUP BY merk (per automerk), en MAX(huurprijs) (maximale huurprijs).

Wat gebeurt er dan als er 2 verschilende visitors op dezelfde tijd langskwamen?

Niks, want je doet GROUP BY visitor, je hebt dus gewoon 2 losse groepjes voor die 2 visitors met de tijd (die al dan niet hetzelfde is)

[ Voor 5% gewijzigd door RobertMe op 27-04-2009 14:16 ]

[curry684]

Noxious schreef op maandag 27 april 2009 @ 13:51:
Je wilt dus zeggen dat MySQL met een standaardconfiguratie niet goed geconfigureerd is?

MySQL's default configuration is op veel punten ingericht op backwards compatibility tot in de kist, en staat daarmee een hoop ranzige vreemdigheden toe uit de tijd dat MySQL nog brakker was dan tegenwoordig - zoals malformed GROUP BY clauses.

[Noxious]

RobertMe schreef op maandag 27 april 2009 @ 14:15:
[...]

Dat hoeft niet, omdat MAX op de GROUP BY werkt, dat mag dus wel (MAX selecteerd de maximum waarde van de gegeven kolom, in dat groepje).

edit:
Hoeft niet, als in, je kunt gewoon GROUP BY visitor doen

Voorbeeld van phpfreakz:
Selecteer de maximale huurprijs per automerk, dus GROUP BY merk (per automerk), en MAX(huurprijs) (maximale huurprijs).

Maar ik neem aan dat er toch wat moet veranderen aan m'n query
Bij m'n SELECT geef ik dus geen MAX op maar gewoon SELECT visitor,whenvis en doe ik verderop GROUP BY visitor, MAX(whenvis) ofzo?

Niks, want je doet GROUP BY visitor, je hebt dus gewoon 2 losse groepjes voor die 2 visitors met de tijd (die al dan niet hetzelfde is)

Ahja tuurlijk, tnx

[RobertMe]

Noxious schreef op maandag 27 april 2009 @ 13:51:

SQL:

SELECT visitor,MAX(whenvis) AS whenvis 
FROM profielvisits 
WHERE visited = <hier de userid> 
GROUP BY visitor 
ORDER BY whenvis DESC 
LIMIT 50;

Deze is gewoon helemaal correct. SELECT visitor mag omdat je GROUP BY visitor hebt, en MAX mag omdat je GROUP BY hebt.

Wat er gebeurd:

1. Haal alle records uit de tabel profielvisits
2. Filter vervolgens op visited
3. Maak dan per visitor een groepje
4. Selecteer de visitor van dat groepje, en de maximale whenvis
5. (en sorteer dat op whenvis)

[Noxious]

Maar.. er is niets aangepast tov de vorige query? (behalve dat profielvisits. niet meer voor de veldnaam staat in de SELECT en dat de ORDER BY nu whenvis pakt ipv MAX(whenvis) aangezien dit waarschijnlijk wat sneller is).

Toch fijn dat 'ie dan al correct was

Overigens kan het geen kwaad om dit soort dingen (en dat linkje wat iemand ergens meer naar boven gaf met de 'betere' MySQL configuratie, bedankt daarvoor!) na te kijken, in de toekomst gaan we mogelijk over op een andere databaseserver namelijk

[ Voor 32% gewijzigd door Noxious op 27-04-2009 14:36 ]

[RobertMe]

Noxious schreef op zaterdag 25 april 2009 @ 16:29:
Toevoeging: mijn query ziet er nu dus zo uit:

SQL:

SELECT profielvisits.visited,profielvisits.visitor,MAX(profielvisits.whenvis) AS whenvis
FROM profielvisits
WHERE visited = <hier de userid>
GROUP BY visitor
ORDER BY MAX(profielvisits.whenvis) DESC
LIMIT 50;

Toen stond er ook nog profielvisits.visited bij de SELECT, en die mag niet, omdat je hem niet in de GROUP BY noemt.

[Noxious]

aah zo, mja ik bedacht dat ik die helemaal niet nodig had, aangezien ik die al als input gaf
Maar whenvis staat toch ook niet bij de group? dat puntje begreep ik nog niet helemaal

@RobIII hieronder: AH! Dan heb ik blijkbaar meerdere malen over de niet heen gelezen. Tnx!

Heel erg bedankt iedereen

[ Voor 26% gewijzigd door Noxious op 27-04-2009 14:43 ]

[RobIII]

We kunnen lang en breed lullen maar er is gewoon een simpele regel voor:

Alles wat niet in aggregate functies in de select is opgenomen moet in de group by.

Simpel.

[ Voor 31% gewijzigd door RobIII op 27-04-2009 14:44 ]

[RobertMe]

in de toekomst gaan we mogelijk over op een andere databaseserver namelijk

Mag ik dan hinten naar PostgreSQL? Zelf tevreden gebruiker van, alles volgens de SQL standaarden (zo is volgens de SQL standaard || concat, terwijl in MySQL || OR betekend, kun je in MySQL een JOIN doen in een DELETE of UPDATE query, en zijn er nog genoeg rare dingen), heeft maar 1 database engine, die alles kan wat MyISAM en inoDB ook kunnen (zoeken, transacties, foreign key's) en nog een een heel lijst met dingen die beter zijn dan in MySQL of features die elke professionele dbserver heeft, maar MySQL niet.

[RobIII]

RobertMe schreef op maandag 27 april 2009 @ 14:44:
[...]

Mag ik dan hinten naar PostgreSQL? Zelf tevreden gebruiker van, alles volgens de SQL standaarden (zo is volgens de SQL standaard || concat, terwijl in MySQL || OR betekend, kun je in MySQL een JOIN doen in een DELETE of UPDATE query, en zijn er nog genoeg rare dingen), heeft maar 1 database engine, die alles kan wat MyISAM en inoDB ook kunnen (zoeken, transacties, foreign key's) en nog een een heel lijst met dingen die beter zijn dan in MySQL of features die elke professionele dbserver heeft, maar MySQL niet.

En waarom is PostgreSQL dan dé optie boven MySQL? Waarom niet MSSQL, Oracle, whatever? Wat je hier noemt hebben alle andere RDBMSen ook. Het is juist MySQL die de vreemde eend in de bijt is.

[RobertMe]

Ik zeg niet dat het dé optie is, maar een van de, voordeel van PostgreSQL is dan weer dat het gratis is en opensource (opensource niet zo'n sterk argument, want daar kijk je als normale gebruiker toch niet naar). Ja, ik weet dat je er ook een gratis variant van Oracle is, maar daar zitten dacht ik ook weer wat haken en ogen aan (max X aantal GB per database/server dacht ik in ieder geval)

[RobIII]

RobertMe schreef op maandag 27 april 2009 @ 14:48:
Ja, ik weet dat je er ook een gratis variant van Oracle is, maar daar zitten dacht ik ook weer wat haken en ogen aan (max X aantal GB per database/server dacht ik in ieder geval)

En van MSSQL ook

[Noxious]

Aangezien het op Linux servers draait nu, en wel zal blijven draaien is de eerste optie voor ons tot nu toe ook PostgreSQL
Later als het qua support en performance nodig is komt er mogelijk een Oracle server, maar dat is nog even te prijzig

[RobertMe]

Dat dacht ik al, maar wist het niet zeker. Maar bij MSSQL zit je dan weer vast aan een Windows server, en bij express zit je met allemaal die losse bestandjes en volgensmij zit je met beheer dan sowieso nogal raar, omdat je denk ik niet 1 gebruiker (db gebruiker, geen windows gebruiker) in meerdere express databases kan gebruiken.

[RobIII]

RobertMe schreef op maandag 27 april 2009 @ 14:54:
Maar bij MSSQL zit je dan weer vast aan een Windows server

True; hoewel het niet perse een server variant van het windows OS hoeft te zijn. En je zit aan Windows vast voor het draaien van MSSQL; de clients boeit (uiteraard) niet (of amper).

RobertMe schreef op maandag 27 april 2009 @ 14:54:
en bij express zit je met allemaal die losse bestandjes

Nee hoor En al zeker niet meer dan bij elk ander RDBMS (je hebt gewoon een mdf en een ldf tenzij je nog meer dan 1 filegroup gebruikt en dat is by default niet zo).

RobertMe schreef op maandag 27 april 2009 @ 14:54:
en volgensmij zit je met beheer dan sowieso nogal raar, omdat je denk ik niet 1 gebruiker (db gebruiker, geen windows gebruiker) in meerdere express databases kan gebruiken.

Jawel hoor
Als je nou eerst eens wat vooronderzoek pleegt voor je dit soort beweringen maakt...

[ Voor 13% gewijzigd door RobIII op 27-04-2009 15:20 ]

[RobertMe]

RobIII schreef op maandag 27 april 2009 @ 15:08:
Nee hoor En al zeker niet meer dan bij elk ander RDBMS (je hebt gewoon een mdf en een ldf tenzij je nog meer dan 1 filegroup gebruikt en dat is by default niet zo).

Maar die mdf en ldf bestandjes "slingeren" dan wel overal rond op de server, terwijl je ze bij een "echte" server gewoon in een map bij elkaar hebt staan

RobIII schreef op maandag 27 april 2009 @ 15:08:
Als je nou eerst eens wat vooronderzoek pleegt voor je dit soort beweringen maakt...

ik wist dat dat beetje wat ik MSSQL Express nu op school gebruikt heb te weinig zou zijn om die reactie te plaatsen * RobertMe doet in het vervolg wat meer onderzoek voor onzin te vertellen

[RobIII]

RobertMe schreef op maandag 27 april 2009 @ 15:18:
[...]

Maar die mdf en ldf bestandjes "slingeren" dan wel overal rond op de server, terwijl je ze bij een "echte" server gewoon in een map bij elkaar hebt staan

Nee dus. Dat is met MSSQL Express net zo goed mogelijk (en ook default behaviour).

[ Voor 24% gewijzigd door RobIII op 27-04-2009 15:23 ]

[curry684]

SQL Server Express is gewoon volstrekt identiek aan de SQL Server Standard Edition van 5000 euro per server met een aantal hardcoded beperkingen in de engine (concurrent connections en max database size met name) en een gelimiteerdere licentie, en de limitatier eruit dat het alleen op server OS'en kan draaien net als de Developer Edition.