Better way to do this recursive query for second oldest result

Question

This works fine but I copied & pasted the segment from the other SO post as noted in the code comments. Is there a better way to do this?

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE GoalType ([GoalTypeID] int, [Type] varchar(25));
INSERT INTO GoalType ([GoalTypeID], [Type]) VALUES 
    (1, 'Price'),
    (2, 'Revenue'),
    (3, 'Type2 Revenue'),
    (4, 'Graduation Percentage'),
    (5, 'Retention Percentage')
;

CREATE TABLE Goal ([GoalID] int, [GoalTypeID] int, [GymID] int, [Month] int, [Year] int, [Goal] int, [Created] datetime, [CreatedBy] int);
INSERT INTO Goal ([GoalID], [GoalTypeID], [GymID], [Month], [Year], [Goal], [Created], [CreatedBy]) VALUES
    (1, 1, 8, 10, 2014, 3500, '2014-10-14 11:09:03', 12345),
    (2, 1, 8, 10, 2014, 2500, '2014-10-14 11:09:04', 12345),
    (3, 1, 8, 10, 2014, 1500, '2014-10-14 11:09:05', 12345),
    (4, 1, 8, 9, 2014, 3500, '2014-10-14 11:09:06', 12345),
    (5, 1, 8, 9, 2014, 2500, '2014-10-14 11:09:07', 12345),
    (6, 1, 8, 9, 2014, 1500, '2014-10-14 11:09:08', 12345),
    (7, 1, 4, 10, 2014, 3500, '2014-10-14 11:09:09', 12345),
    (8, 1, 4, 10, 2014, 2500, '2014-10-14 11:09:10', 12345),
    (9, 1, 4, 10, 2014, 1500, '2014-10-14 11:09:11', 12345),
    (10, 2, 4, 10, 2014, 2500, '2014-10-14 11:09:12', 12345),
    (11, 3, 4, 10, 2014, 1500, '2014-10-14 11:09:13', 12345)
;

Query 1:

--CREATE View ViewGoal AS 
Select 
  m2.GoalID,
  m2.GoalTypeID,
  b.Type,
  m2.GymID,
  m2.Month,
  m2.Year,
  m2.Goal,
  m2.Created,
  m2.CreatedBy,
  m3.GoalID As LastGoalID,
  m3.Goal As LastValue,
  m3.Created AS LastSet,
  m3.CreatedBy as LastSetBy
FROM 
  (
    SELECT m.*,row_number() over (
    partition by GoalTypeID,GymID,Month,Year
    order by Created desc,GymID,Month,Year) as rn
    From Goal m
  ) m2 -- thanks https://stackoverflow.com/a/1049835/121668
JOIN 
  GoalType b 
  ON m2.GoalTypeID = b.GoalTypeID
LEFT JOIN 
  (
    SELECT m.*,row_number() over (
    partition by GoalTypeID,GymID,Month,Year
    order by Created desc,GymID,Month,Year) as rn
    From Goal m
  ) m3
  ON m2.rn+1 = m3.rn 
    AND m2.GoalTypeID = m3.GoalTypeID 
    AND m2.GymID = m3.GymID 
    AND m2.Month = m3.Month 
    AND m2.Year = m3.Year
WHERE m2.rn=1

Results:

| GOALID | GOALTYPEID |          TYPE | GYMID | MONTH | YEAR | GOAL |                        CREATED | CREATEDBY | LASTGOALID | LASTVALUE |                        LASTSET | LASTSETBY |
|--------|------------|---------------|-------|-------|------|------|--------------------------------|-----------|------------|-----------|--------------------------------|-----------|
|      9 |          1 |         Price |     4 |    10 | 2014 | 1500 | October, 14 2014 11:09:11+0000 |     12345 |          8 |      2500 | October, 14 2014 11:09:10+0000 |     12345 |
|      6 |          1 |         Price |     8 |     9 | 2014 | 1500 | October, 14 2014 11:09:08+0000 |     12345 |          5 |      2500 | October, 14 2014 11:09:07+0000 |     12345 |
|      3 |          1 |         Price |     8 |    10 | 2014 | 1500 | October, 14 2014 11:09:05+0000 |     12345 |          2 |      2500 | October, 14 2014 11:09:04+0000 |     12345 |
|     11 |          3 | Type2 Revenue |     4 |    10 | 2014 | 1500 | October, 14 2014 11:09:13+0000 |     12345 |     (null) |    (null) |                         (null) |    (null) |
|     10 |          2 |       Revenue |     4 |    10 | 2014 | 2500 | October, 14 2014 11:09:12+0000 |     12345 |     (null) |    (null) |                         (null) |    (null) |

score 1 · Answer 1 · answered Oct 15 '14 at 13:17

Try this method using LEAD.

;WITH CTE AS
(
SELECT M.*,ROW_NUMBER() OVER (
    PARTITION BY GOALTYPEID,GYMID,MONTH,YEAR
    ORDER BY CREATED DESC,GYMID,MONTH,YEAR) AS RN,
    LEAD(GOALID) OVER(PARTITION BY GOALTYPEID,GYMID,MONTH,YEAR
    ORDER BY CREATED DESC,GYMID,MONTH,YEAR )AS LASTGOALID,
    LEAD (GOAL)  OVER(PARTITION BY GOALTYPEID,GYMID,MONTH,YEAR
    ORDER BY CREATED DESC,GYMID,MONTH,YEAR )AS LASTVALUE,
    LEAD (CREATED)  OVER(PARTITION BY GOALTYPEID,GYMID,MONTH,YEAR
    ORDER BY CREATED DESC,GYMID,MONTH,YEAR )AS LASTSET,
    LEAD (CREATEDBY)  OVER(PARTITION BY GOALTYPEID,GYMID,MONTH,YEAR
    ORDER BY CREATED DESC,GYMID,MONTH,YEAR )AS LASTSETBY
     FROM GOAL M

)
SELECT GOALID,A.GOALTYPEID,B.TYPE,GYMID,
       MONTH,YEAR,GOAL,CREATED,CREATEDBY,
       LASTGOALID,LASTVALUE,LASTSET,LASTSETBY
FROM   CTE A JOIN GOALTYPE B
       ON A.GOALTYPEID = B.GOALTYPEID
WHERE  A.RN = 1

I realize I had SQL Server 2012 selected in SQL Fiddle. My bad. I noticed this was faster in SQL 2012 Fiddle though — gooddadmike, Oct 15 '14 at 14:29

Better way to do this recursive query for second oldest result

1 Answers1