[SQL] Laagste waarde van unieke selectie

[G F0rce 1]

Hoi iedereen,

Even een vraagje van mijn kant. Ik zit met een SQL probleem waar ik niet uit kom. Het gaat om een MySQL database. De tabel ziet er als volgt uit:

Naam	Type	Prijs
Test naam1	Test type1	2
Test naam1	Test type2	3
Test naam2	Test type3	1
Test naam2	Test type4	2

Nu wil ik de laagste Prijs voor elke unieke Naam hebben, maar ook de kolom Type die hoort bij die combinatie van Prijs / Naam . Voor de duidelijkheid de bovenstaande structuur / data is een versimpelt voorbeeld behalve Type zijn er nog een tiental meer kolommen horende bij een combinatie van Naam en laagste Prijs die ik wil hebben. De query die ik nu heb is als volgt:

code:

SELECT DISTINCT(Naam), MIN(Prijs), tabel.* FROM tabel GROUP BY Naam

Echter op deze manier is de data die ik krijg van de kolom Type de data die hoort bij de eerste Type van de DISTINCT(Naam) en niet van de combinatie DISTINCT(Naam) / MIN(Prijs) .

Weet iemand hoe ik dit op kan lossen, ik zit werkelijk muur vast! Mijn dank is groot !

[Daspeed]

Sowieso groepeer je niet goed.
Dit al eens gelezen? --> Programming FAQ - SQL

[justmental]

SQL:

select t1.naam, t1.prijs, t1.type
from tabel t1
where t1.prijs = (select min(t2.prijs)
from tabel t2
where t2.naam = t1.naam
)

Omdat je een pihkal quote in je sig hebt staan

[G F0rce 1]

Daspeed schreef op dinsdag 20 januari 2009 @ 16:03:
Sowieso groepeer je niet goed.
Dit al eens gelezen? --> Programming FAQ - SQL

Had ik nog niet gelezen bedankt, het beste waar ik toe kom nadat gelezen te hebben is:

code:

SELECT DISTINCT(Naam), MIN(Prijs), tabel.* FROM tabel GROUP BY DISTINCT(Naam)

Maar dat kan natuurlijk niet...

justmental schreef op dinsdag 20 januari 2009 @ 16:11:

SQL:

select t1.naam, t1.prijs, t1.type
from tabel t1
where t1.prijs = (select min(t2.prijs)
from tabel t2
where t2.naam = t1.naam
)

Omdat je een pihkal quote in je sig hebt staan

Mijn dank is groot ik ga er gelijk mee aan de slag!

offtopic:
Is een meesterlijk boek!

[Y0ur1]

Een DISTINCT en een GROUP BY in 1 query, dat stinkt sowieso. Je zal je toch wat meer moeten verdiepen in aggregatie/group by en het verschil met een DISTINCT. Probeer het volgende eens:

SQL:

SELECT 
    naam, type, MIN(prijs) as min_prijs
FROM 
    table
GROUP BY 
    naam, type

Je vraag is nogal onduidelijk en warrig, kun je exact zeggen wat je wilt? Type kun je weghalen bij de GROUP BY, afhankelijk van wat je nu precies wilt.

[ Voor 20% gewijzigd door Y0ur1 op 20-01-2009 17:03 ]

[RobIII]

Y0ur1 schreef op dinsdag 20 januari 2009 @ 16:55:
Type kun je weghalen bij de GROUP BY, afhankelijk van wat je nu precies wilt.

Euh; niet zomaar. Hoewel MySQL het prima vreet horen alle niet-aggregated velden die in de select clause staan ook in de group by te staan. Dat MySQL het anders ook wel prima vindt zegt meer over MySQL dan over hoe het eigenlijk zou moeten zijn.

quote: http://gathering.tweakers...sages/1105378//#watdoetgb

Het wordt moeilijker als je meerdere kolommen wil hebben. Stel je de volgende query eens voor:

SQL:

SELECT a, b FROM Table GROUP BY a

SQL Server, Oracle en andere fatsoenlijke databases slikken deze query niet. Om SQL2000 vrij te citeren:

quote:
Column 'Table.B' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Waarom zegt ie dat? Kijk eens mee naar de tabel waar we deze query op doen. We vragen hem te groeperen op identieke waarden in kolom A. Echter, als we dat doen, welke waarde van B moeten we dan selecteren bij waarde 1 in A? Daar kan je zowel 1 als 2 bij verwachten in kolom B, en omdat dat niet gespecificeerd is staat de database het terecht niet toe.

MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

Emphasis mine

[ Voor 58% gewijzigd door RobIII op 20-01-2009 17:19 ]

[G F0rce 1]

Ik zal de vraag iets proberen te verduidelijken. De tabel is iets uitgebreider dan in mijn gegeven in de topicstart. Een uitgebreider voorbeeld:

rowid	productnummer	maandprijs	opstartkosten	aantalmaanden	totaleprijs	limiet	verlenging
1	1	30	10	24	730	100	0
2	1	40	15	24	975	50	1
3	2	5	100	24	220	10	1

Waar ik vervolgens naar opzoek ben is per 'productnummer' de laagste 'totaleprijs'. Echter, bij die combinatie van 'productnummer' / en laagste 'totaleprijs' wil ik ook de overige kolommen hebben. In principe werkt het voorstel van justmental goed, echter doordat het gaat om een tabel van zo'n 650.000 records lijkt zijn voorstel te zorgen voor nogal slechte prestaties (3 tot 4 minuten per query) terwijl de keys in orde lijken te zijn.

Het idee lijkt me niet erg moeilijk maar op de één of andere manier kom ik er niet uit. Bedankt voor de tips in ieder geval!

[Daspeed]

wat is de query die je precies uitvoert en welke indexen liggen er?

[G F0rce 1]

Daspeed schreef op dinsdag 20 januari 2009 @ 17:23:
wat is de query die je precies uitvoert en welke indexen liggen er?

Een query heb ik nu nog niet, want die krijg ik dus niet aan de gang. De PK is rowId, verder liggen er indexen op de kolom productnummer en totaleprijs. De index op totaleprijs was logischerwijs een wanhoopspoging om te kijken of dat de oorzaak was van de tablescan.

[justmental]

Ik weet niet welk veld je waarvoor gebruikt, maar in mij voorbeeld zou je een index op 'naam' moeten hebben.
Bij voorkeur zelfs een gecombineerde index op 'naam' en 'prijs', dan hoeft hij de tabel niet meer in voor de subquery.

[Daspeed]

Oké aan de basis van je query heb je dus eigenlijk dit:

SQL:

SELECT
  productnummer,
  min(totaleprijs)
FROM
 `tabel`
group by
  productnummer

Op het moment dat je meer wilt (bijv rowid en/of andere kolommen) heb je een probleem, want logisch gezien kunnen er meer producten zijn met een overeenkomstig productnummer en een max totaleprijs. Welk record ga je er dan bij joinen?


edit: als je die potentieel dubbelen niet erg vindt ben je er denk ik met de oplossing van justmental?

SQL:

select t1.*
from tabel t1
where t1.totaleprijs = (select min(t2.totaleprijs)
from tabel t2
where t2.productnummer= t1.productnummer
)

[ Voor 23% gewijzigd door Daspeed op 20-01-2009 18:08 . Reden: toevoeging ]

[pedorus]

G F0rce 1 schreef op dinsdag 20 januari 2009 @ 17:22:
In principe werkt het voorstel van justmental goed, echter doordat het gaat om een tabel van zo'n 650.000 records lijkt zijn voorstel te zorgen voor nogal slechte prestaties (3 tot 4 minuten per query) terwijl de keys in orde lijken te zijn.

Het idee lijkt me niet erg moeilijk maar op de één of andere manier kom ik er niet uit. Bedankt voor de tips in ieder geval!

Zoek eens op groupwise max. Als die methoden ook nog niet snel genoeg zijn, dan is er met de huidige MySQL versies nog een ranzige truc mogelijk met order by in een subquery, die toevallig de juiste data geeft (select * from (select * from tabel order by naam,prijs) t group by naam), maar dat wil je niet omdat de resultaten niet ongedefinieerd zijn.

[G F0rce 1]

pedorus schreef op dinsdag 20 januari 2009 @ 18:18:
[...]

Zoek eens op groupwise max. Als die methoden ook nog niet snel genoeg zijn, dan is er met de huidige MySQL versies nog een ranzige truc mogelijk met order by in een subquery, die toevallig de juiste data geeft (select * from (select * from tabel order by naam,prijs) t group by naam), maar dat wil je niet omdat de resultaten niet ongedefinieerd zijn.

Bedankt voor de google link / zoekterm. Op basis daarvan ben ik hier uitgekomen. De derde suggestie die daar genoemd word werkt voor mij het beste. Deze is als volgt:

SQL:

SELECT co1.continent, co1.name, co1.population
  FROM Country AS co1
 WHERE co1.population =
       (SELECT MAX(population) AS maxpop
          FROM Country AS co2
         WHERE co2.continent = co1.continent);

Bedankt voor alle hulp!

[DamadmOO]

Kan je niet gewoon een HAVING gebruiken in MySQL?