August 29, 2007 at 12:37 pm
I'm having a problem creating a script to return the most recent rows per ID to a given date. The data is as follows:
declare @cards table (cardid int, activedate datetime, score1 int, score2 int, score3 int, score4 int, score5 int)
insert @cards values (1, '08/16/2007', 435, 236, 134, 431, 128)
insert @cards values (1, '08/02/2007', 467, 237, 463, 132, 258)
insert @cards values (1, '07/13/2007', 236, 235, 147, 478, 239)
insert @cards values (1, '06/12/2007', 335, 294, 352, 546, 248)
insert @cards values (2, '08/10/2007', 345, 510, 500, 401, 165)
insert @cards values (2, '06/12/2007', 237, 567, 420, 311, 157)
insert @cards values (3, '06/12/2007', 234, 617, 205, 498, 195)
insert @cards values (4, '07/24/2007', 642, 258, 137, 516, 466)
insert @cards values (4, '06/12/2007', 268, 405, 206, 301, 152)
insert @cards values (5, '06/12/2007', 305, 198, 457, 306, 604)
I'm looking for the most recent rows before 8/1/2007 for each distinct cardid
If I run:
Select Top 1 cardid, activedate, score1, score2, score3, score4, score5
From @cards
where cardid = 1 and activedate < '2007-08-01'
order by activedate desc
I get :
cardid activedate score1 score2 score3 score4 score5
------ ---------- ------ ------ ------ ------ ------
1 07/13/2007 236 235 147 478 239
which is correct.
I'd like to return the most recent to 8/1/2007 for each cardid which would look like:
cardid activedate score1 score2 score3 score4 score5
------ ---------- ------ ------ ------ ------ ------
1 07/13/2007 236 235 147 478 239
2 06/12/2007 237 567 420 311 157
3 06/12/2007 234 617 205 498 195
4 07/24/2007 642 258 137 516 466
5 06/12/2007 305 198 457 306 604
Can't seem to figure it out, can anyone help?
August 29, 2007 at 12:44 pm
Select cardid, activedate, score1, score2, score3, score4, score5
From (
Select cardid, activedate, score1, score2, score3, score4, score5, row_number() over (partition by cardid order by activedate desc) AS recid
From @cards
) as d where recid = 1
order by activedate desc
N 56°04'39.16"
E 12°55'05.25"
August 29, 2007 at 12:50 pm
Got it, thank you for your help. Works great.
August 29, 2007 at 1:01 pm
select cardid, max(activedate)
from #cards
where activedate < '08/01/2007'
group by cardid
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply