CODEWITHSUNDEEP
sql-server-2008t-sqlgroup-by
Polyfun
Polyfun

Reputation: 9651

How to GROUP and choose lowest value in SQL and associated column

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

Answers (3)

Mureinik
Mureinik

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

Hercule
Hercule

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

Abhay Chauhan
Abhay Chauhan

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

Related Questions