October 16, 2008 at 2:55 pm
I need to return any goals where the most recent rating for each group is lower than the second most recent rating for each group.
e.g. Using the data supplied in the table below, I would see a return of Goal 1 because the most recent rating (10/3) was lower than the second most recent rating (10/2).
Hope this makes sense.
Thanks in advance,
Adam.
CREATE TABLE AH_SCORES
(
DATE DATETIME,
GOAL VARCHAR(32),
RATING INT
)
GO
SET NOCOUNT ON
INSERT AH_SCORES(DATE, GOAL, RATING) VALUES('10/1/08', 'Goal 1', 1)
INSERT AH_SCORES(DATE, GOAL, RATING) VALUES('10/2/08', 'Goal 1', 2)
INSERT AH_SCORES(DATE, GOAL, RATING) VALUES('10/3/08', 'Goal 1', 1)
INSERT AH_SCORES(DATE, GOAL, RATING) VALUES('10/7/08', 'Goal 2', 1)
INSERT AH_SCORES(DATE, GOAL, RATING) VALUES('10/8/08', 'Goal 2', 2)
INSERT AH_SCORES(DATE, GOAL, RATING) VALUES('10/9/08', 'Goal 2', 3)
GO
October 16, 2008 at 3:13 pm
Probably the easiest way to write this would be to use a combination of the ROW_NUMBER function to determine how recent each record per GOAL is, and a common table expression so you can join it to itself easily:
WITH cte (GOAL, RATING, Recentness) AS
(SELECT GOAL, RATING, ROW_NUMBER() OVER (PARTITION BY GOAL ORDER BY DATE DESC) AS Recentness
FROM #AH_SCORES)
SELECT r1.GOAL
FROM cte r1
INNER JOIN cte r2 ON r1.GOAL = r2.GOAL
WHERE r1.Recentness = 1
AND r2.Recentness = 2
AND r1.RATING < r2.RATING
October 17, 2008 at 7:29 am
Great thank you. This works perfectly.
Chris Harshman (10/16/2008)
Probably the easiest way to write this would be to use a combination of the ROW_NUMBER function to determine how recent each record per GOAL is, and a common table expression so you can join it to itself easily:
WITH cte (GOAL, RATING, Recentness) AS
(SELECT GOAL, RATING, ROW_NUMBER() OVER (PARTITION BY GOAL ORDER BY DATE DESC) AS Recentness
FROM #AH_SCORES)
SELECT r1.GOAL
FROM cte r1
INNER JOIN cte r2 ON r1.GOAL = r2.GOAL
WHERE r1.Recentness = 1
AND r2.Recentness = 2
AND r1.RATING < r2.RATING
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply