Finding second most current date...

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 2005 Forums

Transact-SQL (2005)

Finding second most current date...

Author

Topic

Steve Hanzek
Starting Member

2 Posts

Posted - 2009-05-01 : 14:02:44

Using MS Sql server 2005. Have a table containing group id, effective date, and some other information. I want to compare the current information for a group to the prior information, determined by the effective date.
For example:
GroupID Date
00001 01/01/2009
00001 01/01/2008
00001 01/01/2007
00002 01/01/2009
00002 07/01/2008
00002 01/01/2008

The current data for Group '00001' would be the '01/01/2009' record, which I would find with the max function. The prior data would be the second most current date, or '01/01/2008'.

I have this subquery:

Select ga2.groupcode , max(ga2.start_date),
from group_agent ga2 WITH (NOLOCK)
Group by ga2.groupcode

Which gives me the most current information.
But I'm having problems getting the correct syntax for the rest of the query. Any help would be greatly appreciated.

Thanks
Steve

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-01 : 14:40:06

SELECT '00001',' 01/01/2009' UNION ALL
SELECT '00001',' 01/01/2008' UNION ALL
SELECT '00001',' 01/01/2007' UNION ALL
SELECT '00002',' 01/01/2009' UNION ALL
SELECT '00002' ,' 07/01/2008' UNION ALL
SELECT '00002' ,' 01/01/2008'

select id,Date
,[Rank] = row_number() OVER(Partition by id order by date desc)
from @table

Jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources