Query Help ! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help !

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2011-03-07 : 11:58:27

Folks:

I have the data in a table in following format. I would like to get the data (Name, Date, Value) for the second max (largest) date.

TABLE DATA:

NAME, DATE, Value

ABC,3/3/2011,4
ABC,3/4/2011,5
ABC,3/3/2011,6
ABC,3/5/2011,6
ABC,3/7/2011,7
ABC,3/7/2011,3
XYZ,3/7/2011,3
XYZ,3/6/2011,4
PQR,3/1/2011,3
PQR,3/2/2011,7
PQR,3/2/2011,4
PQR,3/5/2011,4
PQR,3/5/2011,3
PQR,3/4/2011,2

OUTPUT SHOULD BE :

ABC,3/5/2011,6
XYZ,3/6/2011,4
PQR,3/4/2011,2

How will be my query be?

Thanks !

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-07 : 12:15:59

select *
from
(
select Name,Date,Value
,[aRank] = dense_rank() over(partition by name order by date desc)
from data
) t

where t.arank = 2

Jim

P.S. do you really have a table with those column names?!

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources