SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kapital123
Starting Member

29 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
52325 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

29 Posts

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

Kapital123
Starting Member

29 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
17655 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
52325 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

29 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
52325 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  
 New 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.09 seconds. Powered By: Snitz Forums 2000