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.
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 DateA 01/01/2013A 23/10/2012B 23/10/2012Z 23/10/2012A 13/06/2012B 13/06/2012A 01/02/2012Q 01/02/2012But I want only say the records for the last 3 most recent dates.That is, I want this:ID DateA 01/01/2013A 23/10/2012B 23/10/2012Z 23/10/2012A 13/06/2012B 13/06/2012I'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 SeqFROM Table)tWHERE Seq<=3[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Kapital123
Starting Member
31 Posts |
Posted - 2013-01-30 : 19:39:03
|
Thankyou |
|
|
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 SeqFROM Table)tWHERE Seq<=3 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
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] |
|
|
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 SeqFROM Table)tWHERE Seq<=3 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
it will still workregardless of whether dates are continuos or not it will always give you latest 3 dates------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Kapital123
Starting Member
31 Posts |
Posted - 2013-02-01 : 00:15:45
|
Thanks for that clarity, I misinterpreted the code. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-01 : 02:42:44
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|