Max (DateTime) - 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

General SQL Server Forums

New to SQL Server Programming

Max (DateTime)

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-16 : 22:03:54

Hi,

I have a table Periods with the following fields
PeriodID GUID
PeriodName varchar(50)
StartDate DateTime
EndDate DateTime.

Now I want to retrieve all information from the Periods table for the 2nd Maximum startdate (2nd latest start date).

Is there any way I can do this? I know there is a top (2) function but it returns both the top startdate and the second startdate. the max function returns only the top date. Is there a Max(2) function that return the 2nd maximum start date?

Thank you

Regards,

Eugene

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-16 : 22:09:11

using row_number() method


select *
from   (
           select PeriodID, PeriodName, StartDate, EndDate,
                  row_no = row_number() over (order by StartDate desc)
           from   table
       ) d
where  row_no = 2

min() of top 2 method


select min(StartDate)
from   (
            select top 2 StartDate
            from   table
            order by StartDate desc
       ) m

KH
[spoiler]Time is always against us[/spoiler]

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-16 : 22:13:05

Ahhh. Thank you KhTan.

The row_no method is excellent. It could be reused if I want to select the 2nd, 3rd or 4th maximum date. I did not thought of it when I am doing the query. Thank you for opening my eyes.

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-16 : 22:32:20

Khtan

I got a question regarding the rownumber.

what if there is only 1 row returned. i.e. there is no 2nd maximum date (only 1 date). What happens when you run the query? Will it return null or the 1st row?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-16 : 22:55:44

There will not be any result return as there will not be any record with row_no = 2.

With the min() of top method, the one and only one record will be returned.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources