[SQL] Query goedkoopste artikel

[-_D_-]

Er is een database E-Commerce met een Article table. Het heeft de volgende kolommen:

artno - Artikelnummer
description - Beschrijving
color - Kleur
size - Maat
price - Prijs

Als ik een artikel uit de database ga halen moeten al deze eigenschappen (kolommen) weergegeven worden.

Nu wil ik in 1 query het goedkoopste en het op 1 na goedkoopste artikel vinden.
Het goedkoopste artikel is 1 exemplaar van (3 euro), maar het op 1 na goedkoopste artikel, dat zijn er drie (van 9 euro).

Dit heb ik als query om de goedkoopste te vinden, maar dan?

SELECT A.artno , A.description, A.color, A.size, A.price
FROM Article A
WHERE A.price = ( SELECT MIN (A.price) FROM Article A)

[Neman]

code:

SELECT A.artno , A.description, A.color, A.size, A.price
FROM Article A
WHERE A.price IN (SELECT DISTINCT A.price FROM Article A ORDER BY A.price LIMIT 2)

zoiets?

edit:

foutje

[ Voor 9% gewijzigd door Neman op 18-06-2003 15:38 ]

[Feyd-Rautha]

Dit is een kortere manier :

code:

SELECT min(Article.price), ...
FROM Article

En voor de opeen na goedkoopste moet je de query sorteren en vervolgens eens naar het TOP statement kijken

[momania]

Je zal moeten bepalen op welke volgorde je gaat sorteren na de prijs.
Dan pas kan je de twee goedkoopste eruit halen.

Dus dan sorteren op prijs + andere velden en met LIMIT (in geval van MySQL) er maar 2 uithalen.

[-_D_-]

Reply van server:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IN'.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'ORDER'.

[Kaastosti]

"SELECT A.artno, A.description, A.color, A.size, A.price FROM Article A ORDER BY A.prince ASC LIMIT 0, 2"

Zou zoiets ook niet werken?
Sorteer de lijst artikelen oplopend op prijs en pak alleen de eerste twee elementen...

[Yo-han]

Waarom niet gewoon:

code:

ORDER BY price DESC LIMIT 2

of zie ik nu iets over het hoofd

[Verwijderd]

zoiets?

code:

ORDER BY PRICE LIMIT 2

[ Voor 4% gewijzigd door Verwijderd op 18-06-2003 15:39 ]

[-_D_-]

Feyd-Rautha schreef op 18 June 2003 @ 15:36:
Dit is een kortere manier :

code:

SELECT min(Article.price), ...
FROM Article

En voor de opeen na goedkoopste moet je de query sorteren en vervolgens eens naar het TOP statement kijken

Die eerste methode had ik ook al bedacht maar hoe krijg ik dan de overige eigenschappen (kolommen) erin? Want als ik na haakje sluiten een komma invoer om de andere eigenschappen in te voeren gaat hij daarober bitchen.

[momania]

-_D_- schreef op 18 June 2003 @ 15:37:
Reply van server:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'IN'.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'ORDER'.

Over wat voor SQL server hebben we het eigenlijk?
En de reply, op welke query was dat en waarom kan die zelf niet ontcijferen?

[-_D_-]

Kaastosti schreef op 18 June 2003 @ 15:37:
"SELECT A.artno, A.description, A.color, A.size, A.price FROM Article A ORDER BY A.prince ASC LIMIT 0, 2"

Zou zoiets ook niet werken?
Sorteer de lijst artikelen oplopend op prijs en pak alleen de eerste twee elementen...

Die had ik ook al bedacht, maar ik heb drie artikelen van dezelfde prijs die allemaal het op 1 na goedkoopste artikel zijn. Dit kan dus niet.

[-_D_-]

dayoman schreef op 18 juni 2003 @ 15:38:
Waarom niet gewoon:

code:

ORDER BY price DESC LIMIT 2

of zie ik nu iets over het hoofd

Zie reply op KaasTosti

[whoami]

Gebruik je SQL server kijk dan eens naar het TOP keyword.
Gebruik je MySQL , kijk dan eens naar LIMIT.

(Zorg er dan wel voor dat je je resultset sorteert).

Als je het goedkoopste artikel wilt vinden, kan dat veel sneller mbhv volgende query:

code:

select min(a.price), a.artno, a.naam
from article a
group by a.artno, a.naam

[Yo-han]

-_D_- schreef op 18 juni 2003 @ 15:39:
[...]


Die had ik ook al bedacht, maar ik heb drie artikelen van dezelfde prijs die allemaal het op 1 na goedkoopste artikel zijn. Dit kan dus niet.

DISTINCT er tussen gooien

offtopic:
LOL@KAASTOSTI

[quote]Kaastosti schreef op 18 June 2003 @ 15:37:
...
ORDER BY A.prince ASC LIMIT 0, 2"
...[/quote]

ORDER BY CHOCO.PRINCE!!

[ Voor 31% gewijzigd door Yo-han op 18-06-2003 15:43 ]

[Neman]

Kaastosti en dayoman zien over het hoofd dat er dan maar twee producten in de resultset kunnen komen, maar er kunnen meerdere producten de (één na) goedkoopste prijs hebben...

[-_D_-]

momania schreef op 18 June 2003 @ 15:39:
[...]

Over wat voor SQL server hebben we het eigenlijk?
En de reply, op welke query was dat en waarom kan die zelf niet ontcijferen?

Goeie vraag, tis geen MySQL server voor zover ik weet. Acces de server vanaf de Uni door een Network drive aan te maken:

Verbinding maken met sequel

Open "Explorer" en klik onder menu "Tools" op "Map Network Drive…".
Tik achter "Folder:" het adres \\SEQUEL\SQL-system.
Klik op "Finish".
Je hebt nu een verbinding gemaakt met de computer SEQUEL. Op deze computer staat de practicum-applicatie.
Als het goed is verschijnt er nu een explorervenster met de inhoud van deze computer (zo niet: open "Explorer" en klik onder "All Folders" op de schijf "SQL-system on `Sequel`".)

Op deze schijf staat onder directory "Mssql7\Binn" het programma "isqlw.exe". Dit is een grafische SQL interface met sequel, de database server.

[Feyd-Rautha]

-_D_- schreef op 18 June 2003 @ 15:38:
[...]


Die eerste methode had ik ook al bedacht maar hoe krijg ik dan de overige eigenschappen (kolommen) erin? Want als ik na haakje sluiten een komma invoer om de andere eigenschappen in te voeren gaat hij daarober bitchen.

Voor aggregate-functions zoals MIN, SOM, COUNT, ... moet je je query steeds GROUP BY'en. Dit was ik eventjes vergeten te vermelden...

[Verwijderd]

Volgens mij zou deze statement het moeten doen:
select artno , description, color, size, price FROM Article
where price = (select min(price) from Article) or
price = (select min(price) from article where price > (select min(price) from article))

[-_D_-]

Verwijderd schreef op 18 June 2003 @ 15:51:
Volgens mij zou deze statement het moeten doen:
select artno , description, color, size, price FROM Article
where price = (select min(price) from Article) or
price = (select min(price) from article where price > (select min(price) from article))

Jij bent Koning!!! Ik wist ff niet wat ik met al die replies aan moest en wilde net weer een nieuwe vraag gaan stellen maar deze query werkt! Dank je wel man!

[whoami]

Verwijderd schreef op 18 June 2003 @ 15:51:
Volgens mij zou deze statement het moeten doen:
select artno , description, color, size, price FROM Article
where price = (select min(price) from Article) or
price = (select min(price) from article where price > (select min(price) from article))

volgens mij zal dat sql-statement helemaal niet het gewenste resultaat afleveren.
(en zal het ook nogal eens serieus in-performant zijn).

[-_D_-]

whoami schreef op 18 June 2003 @ 15:53:
[...]

volgens mij zal dat sql-statement helemaal niet het gewenste resultaat afleveren.
(en zal het ook nogal eens serieus in-performant zijn).

Het geeft dus wel precies wat ik wilde weten en het is ook precies wat ik wilde doen, alleen de uitwerking ervan wist ik even niet

[Verwijderd]

Beetje omslachtig, maar dit zou hem moeten zijn:

SELECT *
FROM Article
WHERE Article.price = ( SELECT MIN (Article.price) FROM Article)
// dit was wat je al had: de goedkoopste
UNION
// hieronder wat erbij komt: de goedkoopste van alles behalve de goedkoopste:
SELECT *
FROM Article
WHERE Article.price = (SELECT MIN(Article.price) FROM
(SELECT *
FROM Article
WHERE Article.price > (SELECT MIN (Article.price) FROM Article))

edit: lol, ik begon te tikken toen er 0 replies waren. ik zie wel handiger oplossingen dan de mijne...

[ Voor 13% gewijzigd door Verwijderd op 18-06-2003 16:04 . Reden: haakje vergeten ]

[-_D_-]

Verwijderd schreef op 18 juni 2003 @ 15:57:
Beetje omslachtig, maar dit zou hem moeten zijn:

SELECT *
FROM Article
WHERE Article.price = ( SELECT MIN (Article.price) FROM Article)
// dit was wat je al had: de goedkoopste
UNION
// hieronder wat erbij komt: de goedkoopste van alles behalve de goedkoopste:
SELECT *
FROM Article
WHERE Article.price = (SELECT MIN(Article.price) FROM
SELECT *
FROM Article
WHERE Article.price > (SELECT MIN (Article.price) FROM Article))

edit: lol, ik begon te tikken toen er 0 replies waren. ik zie wel handiger oplossingen dan de mijne...

Deze werkt ook niet helemaal zie ik Maar toch bedankt!

[Verwijderd]

was een haakje voor een select vergeten. doet het nu wel (in Access tenminste)

en vergeet niet het "commentaar" weg te halen. (kan je commentaar gebruiken in SQL??)

[ Voor 37% gewijzigd door Verwijderd op 18-06-2003 16:06 ]

[-_D_-]

Verwijderd schreef op 18 juni 2003 @ 16:05:
was een haakje voor een select vergeten. doet het nu wel (in Access tenminste)

en vergeet niet het "commentaar" weg te halen. (kan je commentaar gebruiken in SQL??)

Ja ik had de query helemaal aangepast en toen gaf hij nog een errortje op die select. Heb er verder niet meer naar gekeken. Maar nogmaals bedankt voor de moeite, Tweakers ruled

[whoami]

commentaar in SQL doe je als volgt:
--dit is een commentaar regel

[mocean]

Verwijderd schreef op 18 juni 2003 @ 15:51:
Volgens mij zou deze statement het moeten doen:

code:

select artno , description, color, size, price FROM Article
where price = (select min(price) from Article) or
           price = (select min(price) from article where 
           price > (select min(price) from article))

Lijkt me leuk als je de 10 goedkoopste artikelen wil...

Lijkt me leuk als je de artikelen met de 10 laagste prijzen wil...

[ Voor 16% gewijzigd door mocean op 18-06-2003 16:15 ]

[narotic]

code:

SELECT * FROM Article
WHERE price IN (SELECT DISTINCT price FROM Article ORDER BY price LIMIT BY 10)

Werkt dit niet? (volgens mij al eerder ongeveer zo hier neergezet)

[Olaf van der Spek]

Dit is veel makkelijker te doen met twee queries.
select distinct price from article order by price desc limit 2

In $prices zet je alle prijzen die de eerste query terug geeft.

select * from article where price in ($prices).

Of met een zoals hierboven staat.

[ Voor 8% gewijzigd door Olaf van der Spek op 18-06-2003 16:49 ]

[-_D_-]

mocean schreef op 18 juni 2003 @ 16:12:
[...]


Lijkt me leuk als je de 10 goedkoopste artikelen wil...

Lijkt me leuk als je de artikelen met de 10 laagste prijzen wil...

Ja daar zat ik later ook al aan te denken
Maar op zich is dit wel een goede query, het loste iig op wat ik wilde hebben

[-_D_-]

OlafvdSpek schreef op 18 June 2003 @ 16:49:
Dit is veel makkelijker te doen met twee queries.
select distinct price from article order by price desc limit 2

In $prices zet je alle prijzen die de eerste query terug geeft.

select * from article where price in ($prices).

Of met een zoals hierboven staat.

Maar als je de limit op 2 zet, pakt hij dan echt de eerste 2, of de eerste 2 verschillende prijzen (wat dus meerdere artikelen kunnen zijn)? Hmz lijkt me wel aangezien er een distinct bij staat...

[DukeMan]

-_D_- schreef op 18 June 2003 @ 15:41:
[...]


Goeie vraag, tis geen MySQL server voor zover ik weet. Acces de server vanaf de Uni door een Network drive aan te maken:

...

Op deze schijf staat onder directory "Mssql7\Binn" het programma "isqlw.exe". Dit is een grafische SQL interface met sequel, de database server.

Lijkt dus alsof het MS SQL Server 7 is...

[robjanssen]

code:

SELECT A.artno , A.description, A.color, A.size, A.price
FROM Article A 
WHERE A.price IN  (SELECT TOP 2 A.price FROM Article A ORDER BY Price)

[-_D_-]

NTGiant schreef op 18 June 2003 @ 18:43:
[...]

Lijkt dus alsof het MS SQL Server 7 is...

MS SQL is geen MYSQL? Weten we dat ook weer

[-_D_-]

robjanssen schreef op 18 juni 2003 @ 19:15:

code:

SELECT A.artno , A.description, A.color, A.size, A.price
FROM Article A 
WHERE A.price IN  (SELECT TOP 2 A.price FROM Article A ORDER BY Price)

Gaat niet werken als het op 1 na goedkoopste artikel 9 euro kost en er daar 3 verschillende artikelen van zijn, die worden dan niet allemaal vermeld

[Olaf van der Spek]

-_D_- schreef op 18 June 2003 @ 18:32:
Maar als je de limit op 2 zet, pakt hij dan echt de eerste 2, of de eerste 2 verschillende prijzen (wat dus meerdere artikelen kunnen zijn)? Hmz lijkt me wel aangezien er een distinct bij staat...

Ja.

[whoami]

-_D_- schreef op 19 juni 2003 @ 17:23:
[...]


MS SQL is geen MYSQL? Weten we dat ook weer

Hoezo? Denk je dan dat MS (Microsoft) SQL == MySQL?

Gelukkig is het dus zo niet. SQL Server is vele keren beter dan MySQL.
In MySQL is wel goed en snel om eenvoudige queries uit te voeren, maar ondersteund wel geen subqueries, FK's, transactions etc....

MS SQL Server daarentegen is een volwaardig RDBMS.

[-_D_-]

whoami schreef op 19 June 2003 @ 19:04:
[...]


Hoezo? Denk je dan dat MS (Microsoft) SQL == MySQL?

Gelukkig is het dus zo niet. SQL Server is vele keren beter dan MySQL.
In MySQL is wel goed en snel om eenvoudige queries uit te voeren, maar ondersteund wel geen subqueries, FK's, transactions etc....

MS SQL Server daarentegen is een volwaardig RDBMS.

Aha zit het zo Ik wist wel dat er onderscheid was tussen SQL en MySQL maar ik wist niet waarin (mogelijkheden, fabrikant etc). Maar thnx

[narotic]

MySQL is leuk voor webservers met eenvoudige databases en eenvoudige queries. Echter, als het moeilijker wordt (zoals views, nested queries, transactions etc.) kan ie t niet meer. Dan is MSSQL of Oracle beter.

Het goede nieuws is dat er in MySQL 4.x al wel een aantal uitgebreide mogelijkheden zijn, zoals bijv. views als ik me niet vergis.

[whoami]

narotic schreef op 20 juni 2003 @ 12:24:
MySQL is leuk voor webservers

Een webserver met een database erop?
Ok het kan, maar het hoeft niet altijd. Je bedoeld misschien dat MySQL goed geschikt is voor webapplicaties.

[supersook]

hmm, zit eigenlijk met een soortgelijk probleempje met MySQL, dus ga ik maar geen nieuw topic openen, post het zolang hier wel ff:

heb in een table allemaal producten, met een ProductID als product nr en ID als index (unique). Het kan dus voorkomen dat er voor ProductID = 123 meerdere rows voorkomen met een andere index (ID). Nu wil ik in 1 query van alle producten steeds de goedkoopste ophalen, dus voor een product dat 3x voorkomt, degene met de laagste totaalprijs.

ik heb de volgende query:

code:

select DISTINCT ProductID from producten order by totaalprijs asc

Volgens mij geeft deze query wel de juiste hoeveelheid rows (dus alleen de goedkoopste), maar op deze manier kan ik natuurlijk nooit kijken welk ID de row heeft, want als ik de volgende query doe:

code:

select DISTINCT ProductID, ID from producten order by totaalprijs asc

Geeft ie vrolijk alle producten weer, omdat er geen duplicaten meer in voorkomen (ze hebben immers allemaal een uniek ID)

dus: hoe kan ik wel de duplicaten wegfilteren en ook gelijk de ID's ophalen van de geselecteerde rows?

[ Voor 3% gewijzigd door supersook op 20-06-2003 13:26 ]

[supersook]

een kleine kick

[robjanssen]

Deze dan:

code:

SELECT A.artno , A.description, A.color, A.size, A.price
FROM Article A 
WHERE A.price IN  (SELECT TOP 2 DISTINCT A.price FROM Article A ORDER BY Price)

[supersook]

robjanssen schreef op 21 June 2003 @ 09:37:
Deze dan:

code:

SELECT A.artno , A.description, A.color, A.size, A.price
FROM Article A 
WHERE A.price IN  (SELECT TOP 2 DISTINCT A.price FROM Article A ORDER BY Price)

zal ik dan toch maar een eigen topic openen

[DeverauX]

supersook schreef op 20 June 2003 @ 13:24:
dus: hoe kan ik wel de duplicaten wegfilteren en ook gelijk de ID's ophalen van de geselecteerde rows?

afaik is dit niet mogelijk. Als je de duplicaten eruit filtert (met distinct of met een group by) kan je de id's hiervan niet benaderen. Wat je wel kan doen is een group by op productID in combinatie met de functie MIN () om de laagste prijs van een product op te halen.

SQL:

SELECT productID, ID, MIN(totaalprijs) AS laagsteprijs 
FROM producten 
GROUP BY productID 
ORDER BY totaalprijs asc

[robjanssen]

Volgens mij werkt die van mij ook gewoon.

Je hoeft toch geen ID's op te halen?
Je vergelijkt toch een prijs met een prijs?

[supersook]

DeverauX schreef op 21 June 2003 @ 09:46:
[...]

afaik is dit niet mogelijk. Als je de duplicaten eruit filtert (met distinct of met een group by) kan je de id's hiervan niet benaderen. Wat je wel kan doen is een group by op productID in combinatie met de functie MIN () om de laagste prijs van een product op te halen.

SQL:

SELECT productID, ID, MIN(totaalprijs) AS laagsteprijs 
FROM producten 
GROUP BY productID 
ORDER BY totaalprijs asc

de prijs die opgehaald wordt is prima, alleen klopt het ID niet > ofwel, de prijs hoort bij een ander ID, maar is wel de laagste.
Ben zelf al een beetje aan het goochelen geweest, maar krijg het niet goed: iemand die het wel ziet?

[xoror]

narotic schreef op 20 June 2003 @ 12:24:
MySQL is leuk voor webservers met eenvoudige databases en eenvoudige queries. Echter, als het moeilijker wordt (zoals views, nested queries, transactions etc.) kan ie t niet meer. Dan is MSSQL of Oracle beter.

Het goede nieuws is dat er in MySQL 4.x al wel een aantal uitgebreide mogelijkheden zijn, zoals bijv. views als ik me niet vergis.

volgens
http://www.mysql.com/doc/en/ANSI_diff_Views.html komt het pas in 5.1 dat gaat nog heeeeeel lang duren hehe let op 4.1 is nu pas in alpha.

supersook: je output kan ook niet correct zijn. de groupering die je nu heb slaat nergens op.
kijk maar eens hier naar: http://vulcanus.its.tudelft.nl/~acm/got/antimysql.php en vooral punt 9. mysql is gewoon te los wat dat betreft. Het gaat vaak goed, maar heel vaak ook niet.

[supersook]

xoror schreef op 21 juni 2003 @ 11:15:
supersook: je output kan ook niet correct zijn. de groupering die je nu heb slaat nergens op.
kijk maar eens hier naar: http://vulcanus.its.tudelft.nl/~acm/got/antimysql.php en vooral punt 9. mysql is gewoon te los wat dat betreft. Het gaat vaak goed, maar heel vaak ook niet.

Daar was ik dus inderdaad al achter gekomen, maar heeft iemand een idee hoe ik MySQL weer in het gareel kan krijgen met deze query, of zal ik hiervoor toch steeds per product een losse query moeten gaan doen (niet echt wenselijk, maar je moet iets)?

[ACM]

supersook schreef op 21 June 2003 @ 11:36:
Daar was ik dus inderdaad al achter gekomen, maar heeft iemand een idee hoe ik MySQL weer in het gareel kan krijgen met deze query, of zal ik hiervoor toch steeds per product een losse query moeten gaan doen (niet echt wenselijk, maar je moet iets)?

Dit is een query waar je normaliter een subselect voor gebruikt, dus het lijkt me niet dat het heel handig te herschrijven is naar een gewone select in mysql.

[supersook]

ACM schreef op 21 June 2003 @ 11:49:
[...]

Dit is een query waar je normaliter een subselect voor gebruikt, dus het lijkt me niet dat het heel handig te herschrijven is naar een gewone select in mysql.

en dat ondersteund mysql 3.x dus nog niet, zucht... dan maar op de de ranzige manier, dacht ik ook eens iets netjes te doen