[mysql] group en order

[killercow]

Ik heb een setje tabellen waaruit ik een klein gedeelte data wil pakken te weten:

code:

+ = table, - = column

+adres
-id
-adres (unique)
-plaats

+vermelding
-id
-adres_id
-info
-source_id
-adddate

+sources
-id
-name
-rank

Stel dat ik 2 maal een vermelding heb voor 1 adres, dan wil ik de vermelding tonen waarvan de source_id de hoogste rank heeft, en daarna sorteren op adddate binnen de overgebleven vermeldigen .

Als ik nu echt een query pak als deze:

SQL:

select 
                    adressen.adres ,
                    adressen.id as adres_id,
                    vermeldingen.adddate as adddate,
                    sources.rank as source_rank,
                    sources.name as source_name,
                from 
                    adressen,
                    vermeldingen,
                    sources
                where 
                    vermeldingen.adres_id=adressen.id and 
                    adressen.plaats_id=1 and
                    sources.id=vermeldingen.source_type

              order by sources.rank desc, vermeldingen.prijs desc
   
                limit 0,20

Dan geeft hij netjes alle juiste adressen terug, maar ook 2 maal de vermelding voor het adres met 2 vermeldingen.
Als ik echter een group by vermeldingen.adres_id voor de order by invoeg krijg ik de verkeerde vermelding terug, de sortering op sources.rank doet z'n werk dan niet meer.
Als ik de group by achter de order by stop doet ie t niet meer en krijg ik een sql error.

Heeft iemand een idee of dit op te lossen is?
Ik heb al wat soortgeleike probemen gevonden hier op GOt,maar daar kwam het telkens uit op een nieuwe column van aan elkaar ge-concatte velden die dan gesort werden, wat natuurlijk belaberde performance geeft.

[ Voor 3% gewijzigd door killercow op 13-07-2007 10:33 ]

[Creepy]

MAX() gebruiken voor sources.rank om alleen de hoogste rank te pakken i.c.m. met een group by?

[ Voor 4% gewijzigd door Creepy op 13-07-2007 10:34 ]

[killercow]

Creepy schreef op vrijdag 13 juli 2007 @ 10:34:
MAX() gebruiken voor sources.rank om alleen de hoogste rank te pakken i.c.m. met een group by?

dat werkt inderdaad als ik sources.rank zou willen hebben, het punt is dat het me niet om de waarde van sources.rank gaat, maar source.rank als sortering wil gebruiken om de juiste vermelding te pakken, de data per vermelding is accurater naarmate de rank van de source hoger is, en dus wil ik de hoogste gerankte vermelding hebben.

[killercow]

Iemand nog een idee?
Ik krijg het simpelweg neit voor elkaar in 1 query,
Meer queries is niet tof omdat dat natuurlijk veel zwaarder is.

[Creepy]

Het maakt toch niet uit of je rank nu wel of niet mee selecteert in je query? Zolang je MAX(rank) gebruikt en de juiste group by dan krijg je altijd het record met de hoogste rank (en niet twee per rank zoals je nu krijgt). Sterker nog: in je query selecteer je rank dus dat kan geen probleem zijn.

En met de juiste group by bedoel ik dus alle geselecteerde velden behalve die in een aggrageted function staan (dus MAX e.d.). MySQL staat helaas toe dat je velden dan niet hoeft te groeperen zodat je in die velden random resultaten krijgt.

SQL:

select  
                    adressen.adres , 
                    adressen.id as adres_id, 
                    vermeldingen.adddate as adddate, 
                    max(sources.rank) as source_rank, 
                    sources.name as source_name, 
                from  
                    adressen, 
                    vermeldingen, 
                    sources 
                where  
                    vermeldingen.adres_id=adressen.id and  
                    adressen.plaats_id=1 and 
                    sources.id=vermeldingen.source_type 

              order by sources.rank desc, vermeldingen.prijs desc 
              group by adressen.adres , 
                    adressen.id, 
                    vermeldingen.adddate , 
                    sources.name 
    
                limit 0,20

Of zijn de vermeldingen per adres ook nog eens verschillend?

[ Voor 52% gewijzigd door Creepy op 16-07-2007 11:33 ]

[killercow]

Creepy schreef op maandag 16 juli 2007 @ 11:28:


Of zijn de vermeldingen per adres ook nog eens verschillend?

Per adres bestaan er inderdaad verschillende vermeldingen, de beste vermelding hangt aan de source met de hoogste rank, hoe hoog die rank is maakt niet uit dat hoef ik neit te weten, ik wel er op sorteren zodat ik altijd de beste vermelding pak.

[killercow]

Toch maar eens een kickje,

Is het uberhaupt mogelijk met mySQL om dit in een query te doen? ik blijf tegen de zelfde problemen aanlopen, ongeacht de manier waarop ik t probeer aan te pakken.

[killercow]

Helemaal niemand die ooit zo iets gebouwd heeft?

Nog maar een keer uitleggen dan:

* Ik heb een tabel met adressen, elk adres mag eenmaal vertoond worden.
* Ik heb een tabel met vermeldingen, per adres bestaan soms meer dan een vermelding. ik wil maar een vermelding per adres tonen.
* Ik heb een tabel met bronnen, deze tabel bevat een rank, hoe hoger de rank, hoe beter de bron.
* Per vermelding is opgeslagen bij welk adres ze horen, en bij welke source.
* Nu wil ik dus de hoogst rankende vermelding per adres tonen en deze lijst sorteren op verschillende kolommen binnen de vermelding tabel.

[ACM]

Creepy schreef op maandag 16 juli 2007 @ 11:28:
Het maakt toch niet uit of je rank nu wel of niet mee selecteert in je query? Zolang je MAX(rank) gebruikt en de juiste group by dan krijg je altijd het record met de hoogste rank (en niet twee per rank zoals je nu krijgt).

Je krijgt helemaal geen 'het record' uit een group by... Je hebt geen enkele garantie dat de getoonde maximale rank bij de getoonde sources.name hoort.

Maar dit probleem is met een subquery op te lossen hoor. Is vziw zelfs wel een vrij standaard voorbeeld van hoe je subqueries moet gebruiken, zoiets wordt het dan:

SQL:

select 
                    adressen.adres ,
                    adressen.id as adres_id,
                    vermeldingen.adddate as adddate,
                    sources.rank as source_rank,
                    sources.name as source_name,
                from 
                    adressen,
                    vermeldingen,
                    sources
                where 
                    vermeldingen.adres_id=adressen.id and 
                    adressen.plaats_id=1 and
                    sources.id=vermeldingen.source_type
                   AND sources.rank = (SELECT 
                              MAX(rank) FROM sources
                              WHERE id = vermeldingen.source_type)
              order by sources.rank desc, vermeldingen.prijs desc
   
                limit 0,20

[killercow]

ACM,
Bedankt, ik ga er meteen wat testjes mee maken, hopelijk blijft de performance ook redelijk oke, maar anders moet er gewoon meer hardware komen.

[ACM]

killercow schreef op donderdag 02 augustus 2007 @ 11:47:
Bedankt, ik ga er meteen wat testjes mee maken, hopelijk blijft de performance ook redelijk oke, maar anders moet er gewoon meer hardware komen.

Check eerst dat je de benodigde indices hebt, dan zou het vrij aardig moeten presteren. En je zou evt kunnen proberen om een index op 'sources.id, sources.rank' te zetten, zodat ie voor die subquery de tabel zelf niet hoeft te benaderen.