Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select Current + Last Few Recent Dates
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kapital123
Starting Member

31 Posts

Posted - 01/30/2013 :  01:25:36  Show Profile  Reply with Quote
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

Edited by - Kapital123 on 01/30/2013 01:30:00

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/30/2013 :  02:24:24  Show Profile  Reply with Quote

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

Kapital123
Starting Member

31 Posts

Posted - 01/30/2013 :  19:39:03  Show Profile  Reply with Quote
Thankyou
Go to Top of Page

Kapital123
Starting Member

31 Posts

Posted - 01/30/2013 :  21:54:58  Show Profile  Reply with Quote
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)

Singapore
17689 Posts

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


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/31/2013 :  00:53:54  Show Profile  Reply with Quote
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 - 02/01/2013 :  00:15:45  Show Profile  Reply with Quote
Thanks for that clarity, I misinterpreted the code.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/01/2013 :  02:42:44  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000