Reputation: 9651
This question is an extension to this earlier question (not asked by me).
I have a table consisting of these fields:
id | date_from | date_to | price | priceName
--------------------------------------------------------
CK1 22-12-2012 29-12-2012 800 low
CK1 22-12-2012 29-12-2012 1200 medium
CK2 22-12-2012 29-12-2012 1400 high
CK2 22-12-2012 29-12-2012 1800 very high
CK2 22-12-2012 29-12-2012 2200 extortionate
How do I create a SQL select that groups the results by ID, DATE_FROM, DATE_TO and picks the lowest value from price and the priceName that is associated with the lowest price?
So the result would be
CK1 22-12-2012 29-12-2012 800 low
CK2 22-12-2012 29-12-2012 1400 high
From the previous question I can do:
select id, date_from, date_to, min(price)
from table
group by id, date_from, date_to
How do I extend this to only select the priceName that matches the min(price)? Grouping by priceName won't work because it does not constrain to the min(price).
Upvotes: 0
Views: 110
Reputation: 312404
One way would be to use the rank
window function:
SELECT id, date_from, date_to, price, priceName
FROM (SELECT id, date_from, date_to, price, priceName,
RANK() OVER (PARTITION BY id, date_from, date_to
ORDER BY price ASC) AS rk
FROM mytable) t
WHERE rk = 1
Upvotes: 0
Reputation: 186
You can use subquery.
Select Table.id,Date_from,date_to,MinPrice,PriceName
from
(select id, min(price) as MinPrice
from table
group by id) t1
INNER JOIN table ON t1.id=table.id and t1.MinPrice=table.Price
Group by id,Date_from,date_to,MinPrice,PriceName
Upvotes: 1
Reputation: 414
Try this:
Select t.*,t1.priceName from (
select id, date_from, date_to, min(price)
from table
group by id, date_from, date_to) t
join table t1 on t.id =t1.id
and t.date_from =t1.date_from
and t.date_to =t1.date_to
and t.price =t1.price
Upvotes: 0