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 2008 Forums
 Transact-SQL (2008)
 Select Current + Last Few Recent Dates

Author  Topic 

Kapital123
Starting Member

31 Posts

Posted - 2013-01-30 : 01:25:36
Hello,

Does anyone know how to select the most current date along with the last three dates in a table - whereby the dates are not necessarily sequential and there are multiple records for each date.

Take the following as an example:

ID Date
A 01/01/2013
A 23/10/2012
B 23/10/2012
Z 23/10/2012
A 13/06/2012
B 13/06/2012
A 01/02/2012
Q 01/02/2012

But I want only say the records for the last 3 most recent dates.

That is, I want this:

ID Date
A 01/01/2013
A 23/10/2012
B 23/10/2012
Z 23/10/2012
A 13/06/2012
B 13/06/2012

I've tried a handful of ways, but ultimately I am looking for something 'clean' yet effective. Am trying to avoid too many table joins or apply statements if possible.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-30 : 02:24:24
[code]
SELECT ID,[Date]
FROM
(
SELECT *,DENSE_RANK() OVER (ORDER BY [Date] DESC) AS Seq
FROM Table
)t
WHERE Seq<=3
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Kapital123
Starting Member

31 Posts

Posted - 2013-01-30 : 19:39:03
Thankyou
Go to Top of Page

Kapital123
Starting Member

31 Posts

Posted - 2013-01-30 : 21:54:58
What do I need to modify in this code if my date column is perpetually increasing? Using Seq <= 3 wouldn't work then.

quote:
Originally posted by visakh16


SELECT ID,[Date]
FROM
(
SELECT *,DENSE_RANK() OVER (ORDER BY [Date] DESC) AS Seq
FROM Table
)t
WHERE Seq<=3


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-01-30 : 23:14:09
the Seq is a dense_rank based on ordering by date in descending order. It will always gives you the 3 latest dates


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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 00:53:54
quote:
Originally posted by Kapital123

What do I need to modify in this code if my date column is perpetually increasing? Using Seq <= 3 wouldn't work then.

quote:
Originally posted by visakh16


SELECT ID,[Date]
FROM
(
SELECT *,DENSE_RANK() OVER (ORDER BY [Date] DESC) AS Seq
FROM Table
)t
WHERE Seq<=3


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






it will still work
regardless of whether dates are continuos or not it will always give you latest 3 dates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Kapital123
Starting Member

31 Posts

Posted - 2013-02-01 : 00:15:45
Thanks for that clarity, I misinterpreted the code.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-01 : 02:42:44
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -