Retrieve next to last date SQL Server

user_6072260 0
Viewed 38

I think this is a simple problem but I cannot find an easy answer. I need to retrieve the last 2 entries by date. I have used max() to get the latest date; but do not know how to retrieve the next most recent.

The stored procedure code for latest date is:

SELECT *
FROM Table
WHERE Date=(SELECT MAX(Date) FROM Table);

So using a separate procedure how do I get the next most recent?

max sql-server-2008 sql
2 Answers

You can use order by and top:

select top 2 t.*
from t
order by date desc;
user_11440350

Or just to get the next most recent only as you stated...thus returning only one row...

select top 1 t.*
from t
where t.date != (select max(date) from table)
order by date desc;

or...

with cte as(
select
   t.*
   ,row_number() over (order by t.date desc) as RN
from table t)

select *
from cte
where RN = 2
user_61678550
Related