| Author |
 Topic  |
|
|
Kapital123
Starting Member
14 Posts |
 Posted - 01/30/2013 : 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
|
Edited by - Kapital123 on 01/30/2013 01:30:00
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47060 Posts |
Posted - 01/30/2013 : 02:24:24
|
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/
|
 |
|
|
Kapital123
Starting Member
14 Posts |
Posted - 01/30/2013 : 19:39:03
|
| Thankyou |
|
|
|
Kapital123
Starting Member
14 Posts |
Posted - 01/30/2013 : 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/
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 01/30/2013 : 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
Time is always against us
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47060 Posts |
Posted - 01/31/2013 : 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/
|
|
|
|
Kapital123
Starting Member
14 Posts |
Posted - 02/01/2013 : 00:15:45
|
| Thanks for that clarity, I misinterpreted the code. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47060 Posts |
Posted - 02/01/2013 : 02:42:44
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Select Current + Last Few Recent Dates
