sql server 2008 - Group by, Over or other SQL tools to get value corresp to Min or Max -

my here table

type | price | date chair| 10    | 04/05/2013 chair| 15    | 03/07/2012 chair| 11    |01/01/2011 sofa | 100   |01/08/2011 sofa | 120   |03/09/2013 sofa | 150   |07/07/2010 

for each distinct type pull latest price, result of query should be

type  | price | date chair | 10    | 04/05/2013 sofa  | 120   |03/09/2013 

i tried group , over, far no luck

you can use subquery max(date) each type:

select t1.type,   t1.price,   t1.date yt t1 inner join (   select max(date) date, type   yt   group type ) t2   on t1.type = t2.type   , t1.date = t2.date; 

see sql fiddle demo

alternatively, can use max(date) on (partition ...) this:

select t1.type,   t1.price,   t1.date (   select max(date) on (partition type) maxdate, type, price, date   yt   group type ) t1 t1.date = t1.maxdate; 

sql fiddle demo