[SQL] Records met laagste waarden vinden

[downtime]

Ter introductie: Ik ben een beginnend programmeur (niveau: prutser) die met ASP.NET (C#) en MySQL wat simpele pagina's aan het bouwen is.
Ik kan eenvoudige queries in SQL doen maar ik heb een vraag waar ik al dagen niet uitkom terwijl het niet moeilijk zou moeten zijn. Ik zal het probleem uitleggen aan de hand van een voorbeeld:

Stel, ik heb een platte tabel 'Webwinkels' met tenminste de velden 'ID' (primary key), 'Titel' (tekst), 'Prijs' (waarde), 'Categorie' (tekst), en 'Winkel' (tekst). In die tabel staat het aanbod van enkele webwinkels.

Wat ik al heb is de volgende query:

code:

SELECT ID, Titel, Prijs, Leverancier
FROM Webwinkels
WHERE FIND_IN_SET('Dvd',Categorie) > 0
ORDER BY Titel, Prijs;

En die levert het volgende resultaat op:

code:

ID  Titel Prijs  Winkel

1   Beh   14,95  BlahMovies
2   Beh   15,95  MyMovies
3   Beh   21,95  WebMovies
10  Blah  12,95  InterMovies
11  Blah  13,95  WebMovies
12  Woef  11,95  FlutMovies
14  Woef  21,95  BlahMovies
15  Woef  21,95  AlleMovies

Wat ik wil is dat per unieke "Titel" alleen het record met de laagste prijs wordt getoond. Dus zo:

code:

ID  Titel Prijs  Winkel

1   Beh   14,95  BlahMovies
10  Blah  12,95  InterMovies
12  Woef  11,95  FlutMovies

En daar kom ik niet uit. Ik heb het met termen als LIMIT en DISTINCT geprobeerd maar dat levert niet het gewenste resultaat op.
In het verleden, toen ik ASP en VBS nog gebruikte, heb ik dit opgelost met wat VBS code die het queryresultaat nog eens filterde maar dat is een lompe oplossing die ik dit keer wil vermijden omdat ik weet dat het beter kan.
Zoeken via Google levert niks op omdat ik niet weet op welk keyword ik zou moeten zoeken. Ik heb al op een boel keywords gezocht maar vind voortdurend dingen die ik niet kan gebruiken of dingen die 2 stappen te complex voor me zijn.
Ik zou al heel blij zijn met een goede zoekterm waarmee ik verder kan Googlen maar helemaal perfect zou natuurlijk een echte oplossing zijn.

[Verwijderd]

ORDER BY prijs DESC LIMIT 3 ?

Edit:
Of nee sorry ik lees niet goed

Erm dan moet je meerdere tabellen tegelijk ophalen.

Edit 2:

Eehm nee je hebt maar 1 tabel, dat werkt ook niet, Wat ik je anders aanraad om 2 tabellen te maken, 1 met de titel, en de ander met de data daarvoor, en dan een query maken die eerst de titels ophaald, en vervolgens de laagste prijs erachter zet.

[ Voor 86% gewijzigd door Verwijderd op 16-12-2006 00:29 ]

[downtime]

Verwijderd schreef op zaterdag 16 december 2006 @ 00:27:
ORDER BY prijs DESC LIMIT 3 ?

Edit:
Of nee sorry ik lees niet goed

Dat zou inderdaad een totaal ander resultaat geven

Erm dan moet je meerdere tabellen tegelijk ophalen.

Edit 2:

Eehm nee je hebt maar 1 tabel, dat werkt ook niet, Wat ik je anders aanraad om 2 tabellen te maken, 1 met de titel, en de ander met de data daarvoor, en dan een query maken die eerst de titels ophaald, en vervolgens de laagste prijs erachter zet.

Wat mij betreft ligt m'n tabelstructuur vast. Die is niet perfect maar werkbaar. Ik wil dit echt in de query oplossen en dan bij voorkeur met 1 SELECT statement. Ik ben er echt voor 99% zeker van dat dat kan maar ik weet niet hoe.

[ Voor 4% gewijzigd door downtime op 16-12-2006 00:36 ]

[labee]

code:

SELECT top 3 ID, Titel, Prijs, Leverancier
FROM Webwinkels
WHERE FIND_IN_SET('Dvd',Categorie) > 0
ORDER BY Prijs desc,
                  Titel;

[ Voor 4% gewijzigd door labee op 16-12-2006 00:39 ]

[Verwijderd]

Kijk eens naar de functies MIN() en GROUP BY ;-)

[downtime]

labee schreef op zaterdag 16 december 2006 @ 00:39:

code:

SELECT top 3 ID, Titel, Prijs, Leverancier
FROM Webwinkels
WHERE FIND_IN_SET('Dvd',Categorie) > 0
ORDER BY Prijs desc,
                  Titel;

Dat kan ook niet werken. Correct me when I'm wrong maar als ik 't goed lees geef je zo juist de 3 duurste (order by ... desc) titels en bovendien voorkomt 't niet dat dezelfde titel meerdere keren in die top 3 voorkomt.

Los daarvan is TOP 3 in MySQL volgens mij LIMIT 3 ofzo.

Kijk eens naar de functies MIN() en GROUP BY ;-)

Ik ga morgen kijken... zal wel even duren voor ik weet of 't werkt

[Raku]

Waarom zou je niet van de MIN() functie gebruik maken?

code:

SELECT ID, titel, min( prijs ) , leverancier
FROM webwinkels
GROUP BY Titel

//spuit 11 , venster stond blijkbaar al even open

[ Voor 15% gewijzigd door Raku op 16-12-2006 01:02 ]

[RobIII]

downtime schreef op zaterdag 16 december 2006 @ 00:56:
Ik ga morgen kijken... zal wel even duren voor ik weet of 't werkt

Programming FAQ - SQL >> Hoe werkt dat GROUP BY nu eigenlijk?
Peanuts

[ Voor 7% gewijzigd door RobIII op 16-12-2006 01:00 ]

[_js_]

Dit is iets ingewikkelder dan in de FAQ staat, want hier heb je een subquery of een self join voor nodig.

De query van Simmie werkt niet goed omdat deze een willekeurig id en leverancier geeft, dus niet noodzakelijk het id en de leverancier die bij de prijs horen!

Je zoekt per titel de laagste prijs en met de subquery of de join geef je hier het id en de leverancier bij.

SQL:

SELECT id,titel,prijs,leverancier
FROM webwinkels
WHERE (titel,prijs) = (SELECT titel,MIN(prijs) FROM webwinkels GROUP BY titel)

of

SQL:

SELECT w2.id,w1.titel,min(w1.prijs),w2.leverancier
FROM webwinkels w1
INNER JOIN webwinkels w2 ON w1.titel = w2.titel
GROUP BY w1.titel,w2.id,w2.leverancier,w2.prijs
HAVING w2.prijs = MIN(w1.prijs)

[downtime]

_js_ schreef op zaterdag 16 december 2006 @ 01:10:
Dit is iets ingewikkelder dan in de FAQ staat, want hier heb je een subquery of een self join voor nodig.

De query van Simmie werkt niet goed omdat deze een willekeurig id en leverancier geeft, dus niet noodzakelijk het id en de leverancier die bij de prijs horen!

Bedankt. Jij bent volgens mij de eerste die het probleem helemaal snapt.

Je zoekt per titel de laagste prijs en met de subquery of de join geef je hier het id en de leverancier bij.

SQL:

SELECT id,titel,prijs,leverancier
FROM webwinkels
WHERE (titel,prijs) = (SELECT titel,MIN(prijs) FROM webwinkels GROUP BY titel)

of

SQL:

SELECT w2.id,w1.titel,min(w1.prijs),w2.leverancier
FROM webwinkels w1
INNER JOIN webwinkels w2 ON w1.titel = w2.titel
GROUP BY w1.titel,w2.id,w2.leverancier,w2.prijs
HAVING w2.prijs = MIN(w1.prijs)

Ik ga het nu niet meer testen, want moet morgen vroeg weer op, maar ik denk dat het hiermee wel gaat werken

[Raku]

_js_ schreef op zaterdag 16 december 2006 @ 01:10:
Dit is iets ingewikkelder dan in de FAQ staat, want hier heb je een subquery of een self join voor nodig.

De query van Simmie werkt niet goed omdat deze een willekeurig id en leverancier geeft, dus niet noodzakelijk het id en de leverancier die bij de prijs horen!

Indien er geen dubbele leveranciers zijn met dezelfde titel/prijs werkt de query gewoon. Echter wanneer er meerdere leveranciers zijn word alleen degene die het eerst is meegenomen. In dat geval klopt het ID/Leverancier nog steeds wel.

Je hebt wel gelijk tho , uitgaan van het feit dat er geen 2 dezelfde prijzen voorkomen van een titel is natuurlijk niet echt de beste manier.

[_js_]

Ik zou zeggen, kijk nog eens in de FAQ om te zien hoe GROUP BY nou eigenlijk werkt, want je kennis bevat echt een paar gaten.

code:

mysql> create table webwinkels (id int primary key,titel varchar(20),prijs decim
al(10,2),leverancier varchar(20));
Query OK, 0 rows affected (0.22 sec)

mysql> insert into webwinkels values (1,"ding 1",1.2,"lev 1");
Query OK, 1 row affected (0.02 sec)

mysql> insert into webwinkels values (2,"ding 1",1.1,"lev 2");
Query OK, 1 row affected (0.03 sec)

mysql> select * from webwinkels;
+----+--------+-------+-------------+
| id | titel  | prijs | leverancier |
+----+--------+-------+-------------+
|  1 | ding 1 |  1.20 | lev 1       |
|  2 | ding 1 |  1.10 | lev 2       |
+----+--------+-------+-------------+
2 rows in set (0.00 sec)

mysql> select id,titel,min(prijs),leverancier from webwinkels group by titel;
+----+--------+------------+-------------+
| id | titel  | min(prijs) | leverancier |
+----+--------+------------+-------------+
|  1 | ding 1 |       1.10 | lev 1       |
+----+--------+------------+-------------+
1 row in set (0.02 sec)

mysql>

Zoals je ziet, het id en de leverancier horen niet bij de prijs, precies zoals te verwachten is als je weet wat je doet.

[jochemd]

Dit staat nota bene letterlijk in de MySQL handleiding als een 'groupwise maximum'.

[Coltrui]

En er even nog totaal irrelavante, doch goed bedoelde opmerking achteraan: je tabelnaam deugt niet