August 27, 2009 at 4:57 pm
Hi All please help if you can
I want to evaluate the two most recent rankings for each ID.
My select statement should return only thoes IDs where the 2 most recent rankings are different.
My Select statement should also return the most recent ranking, the date of the most recent ranking and the previous ranking.
I want to insert this select statement in my #RankingChanges table; see below.
For ID A and C the 2 most recent rankings are the same; no results
-For B they are different, return in results
CREATE TABLE #Test
(
ID varchar(1),
Rank int,
TimeStamp datetime
)
INSERT INTO #Test
SELECT 'A',1,'2009-08-27 16:18:42.723' UNION
SELECT 'A',1,'2009-08-27 16:15:42.723' UNION
SELECT 'A',1,'2009-08-27 16:11:42.723' UNION
SELECT 'B',2,'2009-08-27 16:18:42.723' UNION
SELECT 'B',1,'2009-08-27 16:15:42.723' UNION
SELECT 'B',1,'2009-08-27 16:11:42.723' UNION
SELECT 'C',1,'2009-08-27 16:18:42.723' UNION
SELECT 'C',1,'2009-08-27 16:15:42.723' UNION
SELECT 'C',4,'2009-08-27 16:11:42.723'
SELECT * FROM #Test ORDER BY ID,TimeStamp
CREATE TABLE #RankingChanges
(
ID varchar(1),
CurrentRank int,
PreviousRank int,
CurrentRankTimeStamp datetime
)
--This inserts my desired reults
INSERT INTO #RankingChanges SELECT 'B',2,1,'2009-08-27 16:18:42.723'
SELECT * FROM #RankingChanges
DROP TABLE #RankingChanges
DROP TABLE #Test
I have decided on using my ranking changes table rather than deriving the results from the underlying data because I have a huge number of ranks but very few changes.
August 27, 2009 at 10:09 pm
First, I wnat to thank you for taking the time to provide everything you did in setting up your problem. It really helped a lot.
Please let me know if this helps you with you problem:
CREATE TABLE #Test
(
ID varchar(1),
Rank int,
TimeStamp datetime
)
INSERT INTO #Test
SELECT 'A',1,'2009-08-27 16:18:42.723' UNION
SELECT 'A',1,'2009-08-27 16:15:42.723' UNION
SELECT 'A',1,'2009-08-27 16:11:42.723' UNION
SELECT 'B',2,'2009-08-27 16:18:42.723' UNION
SELECT 'B',1,'2009-08-27 16:15:42.723' UNION
SELECT 'B',1,'2009-08-27 16:11:42.723' UNION
SELECT 'C',1,'2009-08-27 16:18:42.723' UNION
SELECT 'C',1,'2009-08-27 16:15:42.723' UNION
SELECT 'C',4,'2009-08-27 16:11:42.723'
SELECT * FROM #Test ORDER BY ID,TimeStamp desc;
with Ranking as (
select
row_number() over (partition by ID order by TimeStamp desc) as RowNum,
ID,
[Rank],
[TimeStamp]
from
#Test
)
select
r1.ID,
r1.[Rank] as CurrentRank,
r1.[TimeStamp] as CurrentTimeStamp,
r2.[Rank] as PreviousRank,
r2.[TimeStamp] as PreviousTimeStamp
from
Ranking r1
inner join Ranking r2
on (r1.RowNum = r2.RowNum -1
and r1.ID = r2.ID)
where
r1.RowNum = 1
and r1.[Rank] r2.[Rank];
DROP TABLE #Test
August 28, 2009 at 1:09 pm
Lynn Pettis (8/27/2009)
Its the least I could do.
This works perfectly. I hadn't used CTE or rownumber so it was also educational.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply