[MySQL] dubbelen filteren m.b.v. prioriteit veld

[remcotolsma]

Ik heb de volgende gegevens in een MySQL tabel staan:

id	name	priority
1	ac	3
2	ab	2
3	ac	2
4	a	1
5	aa	1
6	ab	1
7	ac	1

Nou wil ik graag alle dubbele waarden van het veld 'name' er uit filteren. De velden met de hoogste prioriteiten moeten over blijven. Na het uitvoeren van de query zou dit het resultaat moeten zijn:

Resultaat

id	name	priority
1	ac	3
2	ab	2
4	a	1
5	aa	1

Een GROUP BY moet volgens mij wel gebruikt worden, maar op welk veld moet ik dan een aggregate functie zetten? En welke aggregatie functie moet er dan gebruikt worden? Het zal wel vrij eenvoudig zijn, maar ik kom er niet uit. Google en de MySQL documentatie konden me hiermee ook niet helpen, misschien jullie wel?

SQL:

SELECT id, name, priority 
FROM test 
GROUP BY ? 
ORDER BY priority 

Volgens mij kan het op een eenvoudige manier...

[CH4OS]

SQL:

SELECT `id`, `name`, `priority` 
FROM `test` 
GROUP BY `name` 
ORDER BY `priority`

Weet alleen niet 100% zeker of je aan je GROUP BY-clause ook een ASCend of DESCend kan hangen...
Mocht je dat willen natuurlijk...

Bedenk me nu net, dat je met deze query gewoon alles terug krijgt, dus ook die met een lagere prioriteit en dezelfde name... Denk dat je het beste zoiets kan doen dan:

SQL:

SELECT `id`, `name`, `priority` 
FROM `test`
WHERE `priority` = 3
GROUP BY `name`

Deze query geeft alles terug, waar prioriteit gelijk is aan 3... Weet niet of dat dat de bedoeling is?

Het kan trouwens zijn, dat deze query niet werkt, dan moet je GROUP BY veranderen in ORDER BY... Ik weet alleen nooit wanneer je welke moet gebruiken...

Wat ik eigenlijk niet snap... Waarom update je de priority niet gewoon, als je de name al hebt?
Of word het voor een soort service desk systeem of zo?

[ Voor 72% gewijzigd door CH4OS op 14-06-2006 20:59 ]

[BestTested!]

Wel niet hemaal voor MySQL, maar in MS-SQL kan je dat zo oplossen. Wie weet dat het een goede schop geeft in juiste richting:

code:

SELECT     Name, Priority
FROM        yourTable AS OuterC
WHERE     Priority IN
                          (SELECT     max(Priority)
                            FROM       yourTable
                            WHERE Name = OuterC.Name)

edit:
Correlated subqueries heten ze.. en MySQL ondersteunt ze ook..
http://dev.mysql.com/doc/...orrelated-subqueries.html

[ Voor 16% gewijzigd door BestTested! op 14-06-2006 21:06 ]

[Psychokiller]

Werkt de DISTINCT(name) en een order by priority niet in dit geval ?
Zomaar iets wat me te binnen schoot in ieder geval, verder niet getest dus.

[remcotolsma]

Ik wil alle velden selecteren... dan kan afaik DISTINCT niet gebruikt worden. Ik zal BestTested! zijn oplossing 'ns even uitproberen binnen MySQL.

[ Voor 31% gewijzigd door remcotolsma op 14-06-2006 21:04 ]

[CH4OS]

remcotolsma schreef op woensdag 14 juni 2006 @ 21:03:
Ik wil alle velden selecteren... dan kan afaik DISTINCT niet gebruikt worden.

Waarom wil je dan filteren op priority? Of wil je, zoals je in je voorbeeld query doet, sorteren op priority?

Hou er trouwens rekening mee, dat als je bijv. op een gegeven moment 5000 records in je tabel test hebt staan, dat je query dan veel geheugen gaat vreten; bouw dus wel (alvast) een pager in...

[ Voor 8% gewijzigd door CH4OS op 14-06-2006 21:05 ]

[Psychokiller]

Hmm, toch even een test tabel met dezelfde gegevens aangemaakt, maar een group by name met een order by priority desc is toch wat je wilt:



Misschien dat de ORDER BY nog met een "id ASC" moet uitgebreid worden in het geval dat er meerdere met dezelfde priotiteit zijn. In dit voorbeeld ging het toevallig goed denk ik.

[remcotolsma]

Misschien dat de ORDER BY nog met een "id ASC" moet uitgebreid worden in het geval dat er meerdere met dezelfde priotiteit zijn. In dit voorbeeld ging het toevallig goed denk ik.

Jah het voorbeeld wat ik gaf was misschien niet zo goed. Want in dit voorbeeld gaat je query toevallig wel goed. Veranderer de prioriteit van id 1 maar eens naar 0. Dan geeft hij wel id 1 terug in het resultaat terwijl id 3 (met dezelfde naam: 'ac') een hogere prioriteit heeft.

[ Voor 15% gewijzigd door remcotolsma op 14-06-2006 21:23 ]

[remcotolsma]

Okej ik heb de oplossing van BestTested! aan de praat .

GJ-tje die gaf al aan dat ik in de gaten moest houden dat de query wel eens veel geheugen kan gaan gebruiken. De tabel zal in de toekomst waarschijnlijk wel veel meer als 5000 records bevatten. Ik denk dat het wel in de miljoenen kan gaan oplopen. Dan moet de query eigenlijks ook nog snel gaan... nou zal de query niet voor al die miljoenen records die sub query hoeven te uitvoeren. Door een paar extra condities op een aantal velden (niet zichtbaar in mijn voorbeeld) kan het aantal records altijd wel terug gebracht worden naar een stuk of 100...

Nou noemde GJ-tje dat ik een 'pager' in zou moeten bouwen... wat bedoel je precies met 'pager'... ik kon er namelijk zo snel niks over vinden.

Daarnaast heb ik toevallig nog een andere oplossing voor mijn probleem gevonden. Die werkt met een 'view'...

SQL:

CREATE VIEW test_priority_order AS 
SELECT * 
FROM test 
ORDER BY priority DESC 

Als ik nu deze query uitvoer krijg ik ook het juiste resultaat...

SQL:

SELECT *
FROM test_priority_order
GROUP BY name

Wat is nou handiger / sneller / beter... bovenstaande oplossing of die van BestTested!?

edit:
Ik zie niet dat VIEWS pas sinds MySQL 5 d'r in zitten... en het moet ook onder MySQL 4 kunnen draaien. Dus het is niet echt een oplossing voor mij...

[ Voor 10% gewijzigd door remcotolsma op 14-06-2006 22:33 ]

[Verwijderd]

Wanneer je GROUP BY gebruikt, moeten alle velden die je ophaalt maar niet in de GROUP BY clause zitten geaggregeerd zijn (min(), max(), avg(), etc. of subselects die maar 1 row teruggeven).

Ik heb me er ook wel 's over verbaasd dat MySQL een syntax accepteert zoals Psychokiller gaf, want dat is in strijd met alle SQL- regels, maar blijkbaar geeft MySQL in zulke gevallen ook maar gewoon de eerste waarde die 'ie tegenkomt terug.

[remcotolsma]

Ik heb me er ook wel 's over verbaasd dat MySQL een syntax accepteert zoals Psychokiller gaf, want dat is in strijd met alle SQL- regels, maar blijkbaar geeft MySQL in zulke gevallen ook maar gewoon de eerste waarde die 'ie tegenkomt terug.

Ik verbaasde mij er net ook al over dat dat zo kon.. ik zal in eerste instantie wel de methode van BestTested! gebruiken.

Iig bedankt allemaal!

[_js_]

Waarom een subquery? Je hebt een subquery nodig om eventueel de bijbehorende id op te halen, maar niet om de priority op te halen. Het volgende geeft hetzelfde resultaat als dat van BestTested:

SQL:

SELECT name,MAX(priority)
FROM test
GROUP BY name
ORDER BY 2 DESC