MySQL Having MAX

[Rainmaker1987]

Voor een studytrip heb ik bij thuiskomst een site gemaakt waar alle deelnemers hun foto's op kunnen gooien zodat iedereen ervan kan genieten. Om het wat leuker te maken kunnen er comments geplaatst worden, mensen getagd en ook ratings aan foto's worden gegeven. Als je echter met 20 man op een studytrip gaat komen er veel dubbele foto's online te staan. Dit moet worden gefilterd uiteraard. Nu heb ik al een systeem waarmee dubbelen kunnen worden aangemaakt alleen de filtering lukt mij niet helemaal.

Even de selectiestatement zoals die nu is (zou moeten worden)

SQL:

/* selecteren van alle eigenschappen van de foto's zodat ze weergegeven kunnen worden */
SELECT * FROM photo WHERE 
/*wanneer een foto niet in de dubbelen voorkomt -> werkt */
id NOT IN (SELECT id_photo FROM double_photo) 

OR 
/*wanneer een foto wel in de dubbele voorkomt -> hier zit het probleem */
id IN (SELECT id_photo FROM double_photo, photo WHERE id_photo = photo.id GROUP BY id_doubles HAVING  rating = MAX(rating)) 

ORDER BY date_time ASC Limit 0,100

Met de laatste id IN .... loop ik helaas vast. Het idee erachter is dat ik de foto wil hebben van de dubbelen die de hoogste rating heeft (= eigenschap van photo). Nu zegt ie dat hij rating niet kent, mocht ik die weghalen selecteerd de database alleen maar de foto's die gelijk zijn aan het maximum van alle foto's. Uiteraard wil ik van elke dubbele (per set dus) de foto hebben met de hoogste rating (kan ook 0 zijn als ze er nog geen hebben). Ik denk dat ik er maar een klein stapje van verwijderd ben maar ook via de MySQL site + google en mn boek kom ik er niet uit welke stap ik mis.

edit:

Wat me wel lukt maar niet help is het volgende:
[code=sql]
SELECT id_photo, MAX(rating) FROM double_photo, photo WHERE id_photo = photo.id GROUP BY id_doubles
[/code]

Hiermee selecteer ik de id_photo's die ik wil hebben, helaas kan ik er niets mee omdat de IN statement uiteraard maar een single column wil hebben

[Voor 9% gewijzigd door Rainmaker1987 op 13-06-2011 17:54]

[NMe]

Pak je het probleem niet verkeerd om aan? Als je een manier hebt om te zien dat een foto dubbel is, waarom staat hij dan überhaupt nog in je database? Waarom verwijder je hem dan niet gewoon? En zelfs als je dat niet wil: waarom volstaat het niet om één enkel veldje op te nemen in de foto-tabel genaamd "IsDouble" of liever nog: een veld genaamd "IsDoubleOf" dat verwijst naar het ID van de foto die het eerst geüpload werd?

[Paul]

Je hebt in principe 2 aparte dingen: Unieke foto's, en mensen/tijdstippen/whetever waarop foto's zijn geüpload, en ik denk dat je nu de foto's zelf ook bij dat 2e stuk hebt

[Voutloos]

Pak gewoon altijd degene die het eerst geupload is, en verplaats indien gewenst eenmalig de bestaande comments daarnaar toe. Klaar.

Het betere model is inderdaad om gewoon een IsDuplicateOfId kolom te hebben, en dan kan je daar prima op filteren.


Overigens gaat je HAVING fout omdat deze ná de GROUP BY uitgevoerd wordt, dus je hebt het niet meer over de waarde van 1 row in originele tabel.

[Voor 17% gewijzigd door Voutloos op 13-06-2011 18:45]

[Rainmaker1987]

Excuses, de terminologie was even verkeerd. Met "dubbele" bedoel ik foto's die erg op elkaar lijken (denk aan meerdere mensen die van ongeveer dezelfde positie een foto van de Golden Gate Bridge hebben genomen). Om de groep de mogelijkheid te geven zelf te kiezen welke de beste is moeten de "dubbelen" erin blijven staan zodat wanneer ze willen ze de foto's met de mindere score een hogere kunnen geven. Daarnaast is het een beetje lullig om foto's van iemand anders zomaar te verwijderen, wellicht dat diegene ze ergens anders nog voor wil gebruiken en even online wil laten zien.

@Voutloos: voor zover ik online zie en in mn boek (Fundamentals of database systems) hoort een having clause achter de group by

[Voor 10% gewijzigd door Rainmaker1987 op 13-06-2011 18:54]

[NMe]

Rainmaker1987 schreef op maandag 13 juni 2011 @ 18:47:
Excuses, de terminologie was even verkeerd. Met "dubbele" bedoel ik foto's die erg op elkaar lijken (denk aan meerdere mensen die van ongeveer dezelfde positie een foto van de Golden Gate Bridge hebben genomen). Om de groep de mogelijkheid te geven zelf te kiezen welke de beste is moeten de "dubbelen" erin blijven staan zodat wanneer ze willen ze de foto's met de mindere score een hogere kunnen geven. Daarnaast is het een beetje lullig om foto's van iemand anders zomaar te verwijderen, wellicht dat diegene ze ergens anders nog voor wil gebruiken en even online wil laten zien.

Dan nog volstaat mijn tweede oplossing met een extra veld in de fototabel. Comments haal je dan heel makkelijk op met een simpele conditie: WHERE <id> IN (PhotoId, IsDuplicateOfId), waar <id> uiteraard het id is van de foto die je zoekt. Je hebt dan de comments van dubbele foto's onder elke duplicaat staan.

Rainmaker1987 schreef op maandag 13 juni 2011 @ 18:47:
@Voutloos: voor zover ik online zie en in mn boek (Fundamentals of database systems) hoort een having clause achter de group by

Dat is niet wat hij bedoelt. Een WHERE wordt uitgevoerd vóór de selectie van data waardoor referenties naar de verschillende tabellen nog mogelijk zijn, en hetzelfde geldt voor condities die je opneemt in JOIN .. ON-constructies. HAVING wordt echter pas uitgevoerd ná de selectie van alle data om vervolgens die data nog verder te filteren. De verwijzing die jij wil maken kan dus niet meer op dat punt.

[Voor 24% gewijzigd door NMe op 13-06-2011 18:57]

[Rainmaker1987]

NMe schreef op maandag 13 juni 2011 @ 18:55:
Dat is niet wat hij bedoelt. Een WHERE wordt uitgevoerd vóór de selectie van data waardoor referenties naar de verschillende tabellen nog mogelijk zijn, en hetzelfde geldt voor condities die je opneemt in JOIN .. ON-constructies. HAVING wordt echter pas uitgevoerd ná de selectie van alle data om vervolgens die data nog verder te filteren. De verwijzing die jij wil maken kan dus niet meer op dat punt.

Ik heb hem door, helaas krijg ik dan niet echt voor elkaar wat ik wil. Het is dus niet bijvoorbeeld mogelijk met de volgende tabellen:

Werknemers
id
afdeling

Uitbetalingen
id
employee_id
betaling
date

De werknemer (id) met het hoogste salaris (betaling) per afdeling op te vragen? (ik weet het slechte tabellen op deze manier, maar als voorbeeld)

Want wat ik dus voor elkaar probeer te krijgen is het volgende te selecteren:
- Alle foto's die niet als dubbele te boek staan
- en de van foto's die als "dubbel" te boek staan per set degene met de hoogste rating
Dit wil ik in 1 statement proberen te proppen (welicht fout dus) omdat ik dan kan sorteren op datum en dmv een limit verschillende pagina's te maken (per 100 in dit geval)

@NMe: wat bedoel je precies met comments en volgens mij kan de selectie: "WHERE <id> IN (PhotoId, IsDuplicateOfId)" niet aangezien ze niet evenveel column hebben

- Even een gek idee waarschijnlijk. Is het niet mogelijk om eerst te laten sorteren voordat ik laat groeperen waardoor ik dus op rating kan sorteren (en op aantal views bij gelijke rating om het wat lastiger te maken) en daarna pas te laten grouperen?

[Voor 8% gewijzigd door Rainmaker1987 op 13-06-2011 19:47]

[Precision]

Rainmaker1987 schreef op maandag 13 juni 2011 @ 19:28:
De werknemer (id) met het hoogste salaris (betaling) per afdeling op te vragen? (ik weet het slechte tabellen op deze manier, maar als voorbeeld)

MySQL:

select werknemers.afdeling, u.employee_id, betaling from werknemers join uitbetalingen u on werknemers.id = u.employee_id
where betaling = (select max(betaling) from uitbetalingen where uitbetalingen.employee_id = u.employee_id) order by betaling

Edit: waarom selecteer je niet gewoon dan de unieke foto's en smijt je er met een union de dubbele foto's bij?
Edit2: Zoiets dus?

Foto
fotoId
name
rating
datum
duplicateOfFotoID

MySQL:

select fotoid, name, datum from foto where duplicateoffotoid is null
union
select f2.fotoid, f2.name, f2.datum from foto f1 join foto f2 on f1.fotoid = f2.duplicateoffotoid
where f2.rating = (select max(rating) from foto where duplicateoffotoid = f2.duplicateoffotoid)
order by datum

[Voor 65% gewijzigd door Precision op 13-06-2011 20:13]

[Rainmaker1987]

Dit lijkt me even mn pet te boven gaan, ik ga dit zeker proberen uit te pluizen. Ik heb inmiddels wel een werkend systeem gekregen door eerst de tabel te sorteren en daarna de group by uit te voeren. Waarschijnlijk extreem ranzig, maar de site moet enigszins snel werken (goed ga ik later proberen)

SQL:

SELECT * FROM photo WHERE 
    id NOT IN (SELECT id_photo FROM double_photo) OR 
    id IN (SELECT id_photo FROM (SELECT id_photo, id_doubles, rating FROM double_photo, photo WHERE id_photo = photo.id ORDER BY rating DESC, views DESC) as my_table_tmp GROUP BY id_doubles) 
ORDER BY date_time ASC Limit 0,100

De truc zit hem in de dubbele select. Eerst de selectie maken waardoor ik de rating & views goed sorteer, daarna de group by op de id_doubles.

thanks @all, ik ga zeker de voorstellen doornemen, alleen prioriteit is nu even de selectie snel werkend te krijgen (onder het mom je kan niet good, fast & cheap at the same time, daarom nu even fast & cheap)

[NMe]

Dat werkt alleen bij gratie van MySQL's rampzalige implementatie van GROUP BY.... Programming FAQ - SQL: Hoe werkt dat GROUP BY nu eigenlijk?

[Precision]

Edit: Zo dus:

MySQL:

select * from foto left join foto_double on foto.id = foto_double.id_double where id_double is null
union
select * from foto join foto_double d on foto.id = d.id_double
where rating = (select max(rating) from foto f join foto_double on f.id = foto_double.id_double where foto_double.id_foto = d.id_foto)
ORDER BY date_time ASC Limit 0,100

[Voor 67% gewijzigd door Precision op 13-06-2011 21:02]