Find values from record that had a max value in a group by

I am looking for the MS SQL variant of the oracle 'KEEP DENSE_RANK' construction.

If you have a table like this:

key date item 1 20070101 Apple 1 20070202 Banana 1 20070303 Cherry

The query for retrieving the item from the last date should pick the Cherry, but what would be the most efficient way to do this (without using multiple queries, in oracle I don't need a sub query!).

You would like to do something like:

Select Key, max(Date) as LastDate, PickLastValue(Cherry) on Date desc

from Table

group by Key.

any suggestions?

hans

You can do the same funciton in SQL Server 2005..

Code Snippet

Create Table #data (

[key] Varchar(100) ,

[date] Varchar(100) ,

[item] Varchar(100)

);

Insert Into #data Values('1','20070101','Apple');

Insert Into #data Values('1','20070202','Banana');

Insert Into #data Values('1','20070303','Cherry');

Insert Into #data Values('2','20070101','Apple');

Insert Into #data Values('2','20070202','Banana');

Code Snippet

--Over all

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Order By [date] Desc) rank From #Data

)

Select [key],[date],[item] from CTE Where rank=1

Code Snippet

--For each Key

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Partition By [Key] Order By [date] Desc) rank From #Data

)

Select [key],[date],[item],rank from CTE Where rank=1

|||

If you use sql server 2000 then you have to use the subquery...

Code Snippet

--For Overall

Select * from #data Where [date] in (Select max(date) From #data)

--For Each Key

Select * from #data Data

Join (Select [Key], max(date) date From #data Group By [Key]) Sub On

Sub.[Key]=Data.[Key] and Sub.date=Data.date

|||

This will do!

thanx

hans