Returning second to last date - 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

Returning second to last date

Author

Topic

allanurban
Starting Member

21 Posts

Posted - 2012-08-02 : 05:58:10

Hello

I have another little question that I hope you guys can help me with.

I have a table with four columns. ID, Stat, Date and Value.

For each date and stat I have a value. What I want to return is all stats and values for the second to last date in the table.

I've figured out how to return the correct date, with

SELECT MAX(date) FROM tabel WHERE date < 'some date'

But I need it to be dynamic and the my problem is that the last entered date is no always today. Otherwise 'some date' could just be replaced with the GETDATE() function.

Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-02 : 06:52:11

On SQL 2005 or later, you can use dense_rank function like this:

;WITH cte AS
(
	SELECT 
		*,
		DENSE_RANK() OVER (ORDER BY date DESC) AS RN
	FROM 
		YourTable
)
SELECT * FROM cte WHERE RN = 2;

That assumes that your date column is of data type datetime or small datetime.

If you have a time part to the data in the date column you would need to change the order by clause to one of these:

ORDER BY CAST(date AS DATE) DESC
ORDER BY DATEADD(dd,DATEDIFF(dd,0,date),0) DESC

allanurban
Starting Member

21 Posts

Posted - 2012-08-02 : 07:11:15

Works perfectly. Thanks a bunch.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-02 : 07:14:24

You are very welcome.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources