[MySQL] GROUP BY rijen sorteren

[Verwijderd]

Ik selecteer uit een tabel met producten welke de volgende kolommen bevatten:

- id
- code
- price
- supplier_id

een voorbeeld van data is:

code:

1 A 10 5
2 A 12 6

Met een qeury wil ik de resultaten grouperen op `code`, en binnen de group sorteren op prijs. Hij moet dus de rij met de laagste prijs teruggeven. Dit laatste lukt me niet. Wat ik geprobeerd heb:

code:

SELECT * FROM product GROUP BY code ORDER BY price

De order by wordt genegeerd. Dit werkt alleen als de orderby gelijk is aan de group by volgens de docs.

code:

SELECT MIN(price) as minprice FROM product GROUP BY code ORDER BY price

Hier wordt wel de laagste prijs gegeven, maar niet de goede supplier_id.

Hoe los ik dit op?

[Digihelp ®]

Je wil dus als resultaat het product met de laagste prijs, voor elke code ?

In dat geval ben je er bijna met jouw laatste query. Je vraagt namelijk alleen de laagste prijs op en niet de andere velden. Zo zou het wel moeten werken:

code:

SELECT code, MIN(price) AS minprice, supplier_id
FROM product
GROUP BY code

Die order by is niet echt relevant om de laagste prijs te krijgen, wel om eventueel te sorteren op code met de laagste prijs. Bijv: c 4 euro, a 10 euro, b 15 euro.

[ Voor 22% gewijzigd door Digihelp ® op 18-10-2005 08:21 ]

[P_de_B]

Hoewel het voorbeeld van Digihelp werkt in MySQL is het niet correct SQL gebruik. De supplier_id kolom hoort er eingelijk niet in thuis. Als je GROUP BY gebruikt moeten alle kolommen in de select lijst in de GROUP BY voorkomen, of in een aggregate functie (min, max, avg etc.) staan. Dat MySQL dit wel goedkeurt werkt alleen maar fouten in de hand, je kunt dit beter direct maar afleren.

[whoami]

Met MIN kan je de laagste waarde terugkrijgen binnen een groep, met MAX de hoogste waarde, etc...
Dit staat wel allemaal in iedere Sql tutorial, en ook het stukje in de FAQ over GROUP BY helpt wel.
P&W FAQ - SQL

Wat P_de_B zegt, is ook juist: GROUP BY moet je toepassen op de columns in je select list die geen aggregate functie zijn.

[ Voor 35% gewijzigd door whoami op 18-10-2005 08:38 ]

[whoami]

Heropend.

Zoals ik reeds in de mail uitgelegd heb, maak je hier niet goed gebruik van group by / aggregate functies.

Wil je de laagste prijs per code, dan moet je deze query uitvoeren:

code:

select min(price) as minprice, code
from product
group by code

Wil je nu ook nog weten welke leveranciers het product tegen de laagste prijs aanbieden, dan zal je nog wat extra's moeten doen. Je kan hier nl. niet zomaar de supplier_id gaan bijstoppen.
Dan wordt je query nl dit

code:

select min(price), code, supplier_id
from product
group by code, supplier_id

waardoor er dus ook op supplier_id gegroepeerd wordt. Je kan supplier_id dan ook niet weglaten uit de group by list, want, welke supplier_id wil je dan tonen ? Het kan nl. zijn dat er meerdere leveranciers het product tegen de laagste prijs aanbieden.

Je zult dus iets zoals dit moeten doen:

code:

select product.code, bliep.minprice, product.supplier_id
from product,
       ( select code, min(price) as minprice
          from product
          group by code
       ) as bliep
where product.code = bliep.code
and product.price = bliep.minprice

Ik weet echter niet of mySql zo'n constructie ondersteunt.

Ik vraag me trouwens ook af of je datamodel wel helemaal kosjer is ...

[jochemd]

RTFM

Modbreak:

Gelieve dergelijke replies achterwege te laten

[ Voor 26% gewijzigd door whoami op 18-10-2005 12:33 ]

[Verwijderd]

@whoami: Dank voor je input. MySQL ondersteunt dit vanaf versie 5 helaas, en een extra query probeer ik te vermijden, onder meer om performanceredenen.

Mijn openingspost was in die zin niet duidelijk omdat ik vergat erbij te vermelden dat ik ook de supplier_id mee wil selecteren. Dit is een versimpeld voorbeeld, in het echt zijn er nog een dozijn meer kolommen als supplier_id.
Mijn vraag gaat dus niet om de GROUP BY syntax an sich, maar een oplossing voor de volgende situatie:

een voorbeeld van data is:

code:

1 A 10 5
2 A 12 6
3 B 16 8
4 B 14 9

Als ik een normale SELECT doe op alles, dus krijg ik 4 rijen terug. De tweede kolom (code) mag niet dubbel voorkomen en van de rijen met gelijke code moet de rij geselecteerd worden waar kolom 4 (prijs) het laagste is.

[Verwijderd]

SQL:

SELECT F.*
FROM tblTest AS F 
   INNER JOIN 
       [SELECT tblTest.Code, MIN(tblTest.Price) AS Price 
       FROM tblTest 
       GROUP BY tblTest.Code]. AS M 
   ON (F.Price=M.Price) AND (F.Code=M.Code)

(MSACCESS syntax, ben niet zo thuis in MYSQL)

Voor performance kan je natuurlijk een view aanmaken waar alleen de code en de laagste prijs in zitten, en dan je tabel inner joinen met die view op Price en Code.

[Webgnome]

Verwijderd schreef op dinsdag 18 oktober 2005 @ 13:45:
@whoami: Dank voor je input. MySQL ondersteunt dit vanaf versie 5 helaas, en een extra query probeer ik te vermijden, onder meer om performanceredenen.

Mijn openingspost was in die zin niet duidelijk omdat ik vergat erbij te vermelden dat ik ook de supplier_id mee wil selecteren. Dit is een versimpeld voorbeeld, in het echt zijn er nog een dozijn meer kolommen als supplier_id.
Mijn vraag gaat dus niet om de GROUP BY syntax an sich, maar een oplossing voor de volgende situatie:

een voorbeeld van data is:

code:

1 A 10 5
2 A 12 6
3 B 16 8
4 B 14 9

Als ik een normale SELECT doe op alles, dus krijg ik 4 rijen terug. De tweede kolom (code) mag niet dubbel voorkomen en van de rijen met gelijke code moet de rij geselecteerd worden waar kolom 4 (prijs) het laagste is.

subqueries worden ondersteunt vanaf 4.1

[whoami]

Verwijderd schreef op woensdag 19 oktober 2005 @ 00:51:

SQL:

SELECT F.*
FROM tblTest AS F 
   INNER JOIN 
       [SELECT tblTest.Code, MIN(tblTest.Price) AS Price 
       FROM tblTest 
       GROUP BY tblTest.Code]. AS M 
   ON (F.Price=M.Price) AND (F.Code=M.Code)

Da's dus dezelfde query als die die ik al voorgesteld had.

Voor performance kan je natuurlijk een view aanmaken waar alleen de code en de laagste prijs in zitten, en dan je tabel inner joinen met die view op Price en Code.

Een view kan je idd overwegen, want MySQL ondersteunt een select in de from blijkbaar (nog) niet. Echter, ik ben ook niet zo zeker dat MySQL wel views ondersteund...

Maar, de link die jochemd geeft, doet de job.

[ Voor 4% gewijzigd door whoami op 19-10-2005 09:03 ]