Find max of 5th date in column ex_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

General SQL Server Forums

New to SQL Server Programming

Find max of 5th date in column ex_date

Author

Topic

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2010-12-22 : 05:01:02

Hi All,
I need one small query to go back for 5 max [ex_date] in table

,

TABLE
---------------------------
ISIN sec_id ex_date
---------------------------
142 fs_ff_004 2010-10-02
142 fs_ff_004 2010-05-18
142 fs_ff_004 2010-02-14
142 fs_ff_004 2010-01-18
142 fs_ff_004 2009-12-22
142 fs_ff_004 2009-10-02
------------------------------

output:-

'2009-12-22'

so i want to find this date '2009-12-22' above table

In single query, to find the solution...

Thanks
Mageshkumar.M MCA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-22 : 05:18:05

You can do that using row_number() if you are on SQL Server 2005 or higher.
This is also possible:

SELECT TOP 1 ex_date FROM
(SELECT TOP 5 ex_date FROM Table ORDER BY ex_date DESC)dt
ORDER BY ex_date ASC

No, you're never too old to Yak'n'Roll if you're too young to die.

MageshkumarM
Yak Posting Veteran

61 Posts

Posted - 2010-12-22 : 05:43:10

Hey Thank u man...!

fye
2010-09-30
2010-06-30
2010-03-31
2009-12-31
2009-09-30
2009-06-30
2009-03-31

Query:-
-----
SELECT TOP 1 fye FROM (SELECT TOP 4 fye FROM Quarter where FS_PERM_SEC_ID='B00N7M-S-CH' ORDER BY fye DESC) dt ORDER BY FYE ASC

output:-
fye
---------
2009-12-31

Once again thanks..!

Regards,
Magesh.M

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources