CODEWITHSUNDEEP
sql-server-2008group-by
user1700890
user1700890

Reputation: 7730

Group by, Over or other SQL tools to get value corresp to Min or Max

My here is my 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 I would like to pull the latest price, so the result of my query should be

Type  | Price | Date
chair | 10    | 04/05/2013
sofa  | 120   |03/09/2013

I tried group by and over, but so far no luck

Upvotes: 1

Views: 165

Answers (3)

Taryn
Taryn

Reputation: 247690

You can use a subquery to get the max(date) for each type:

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

See SQL Fiddle with Demo

Alternatively, you can use max(date) over (partition ...) like this:

select t1.type,
  t1.price,
  t1.date
from
(
  select max(date) over (partition by type) maxdate, type, price, date
  from yt
  group by type
) t1
where t1.date = t1.maxdate;

SQL Fiddle with Demo

Upvotes: 4

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT t1.type,
       t1.price,
       t1.date
FROM yt t1
WHERE t1.date = (SELECT max(t2.date)
                 FROM yt t2
                 WHERE t1.type = t2.type)

If Max value is not enough:

SELECT t1.type,
       t1.price,
       t1.date
FROM yt t1
WHERE t1.date = (SELECT TOP 1 t2.date
                 FROM yt t2
                 WHERE t1.type = t2.type
                 ORDER BY t2.date desc)

Result:

|  TYPE | PRICE |                         DATE |
------------------------------------------------
|  sofa |   120 | March, 09 2013 00:00:00+0000 |
| chair |    10 | April, 05 2013 00:00:00+0000 |

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460098

You can use a CTE with ROW_NUMBER/DENSE_RANK:

WITH CTE AS
(
    SELECT  RN = ROW_NUMBER() OVER (PARTITION BY Type ORDER BY Date DESC),
            Type, Price, Date
    FROM dbo.TableName
)
SELECT Type, Price, Date
FROM CTE
WHERE RN = 1

DEMO

ROW_NUMBER returns always exactly one record whereas DENSE_RANK returns all records with the latest Date(if there are multiple with the same MAX-Date).

Upvotes: 3

Related Questions