SQL with multiple ROW_NUMBER or RANK

sql, sql-server-2008

Solution

The most straight forward (ie. readable SQL) answer that I have come up with uses WITH and ROW_NUMBER.

First, make a ROW_NUMBER query that orders the events and gives a number to each event unique to that PersonId:

SELECT *,
    ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY DateStarted DESC) AS EventOrder
FROM PersonEvents

Results:

PersonId    DateStarted              ReasonForLeaving    EventOrder
1           2013-02-12 00:00:00.000  NULL                1
1           2012-04-12 00:00:00.000  holiday             2
1           2011-03-12 00:00:00.000  sick                3
2           2013-06-12 00:00:00.000  NULL                1
2           2012-07-12 00:00:00.000  had enough          2
2           2011-05-12 00:00:00.000  new baby            3
3           2013-09-12 00:00:00.000  NULL                1
3           2011-08-12 00:00:00.000  pregnant            2
4           2012-10-12 00:00:00.000  NULL                1

Now, the "first" event (in my case the most recent) for every person contains the date that the change was made (real-life example: this is student enrolment history data across multiple schools, containing School ID and lots of other guff). The "Second" event for every person contains the previous event and reason for leaving. To add it together:

WITH SortedEvents AS (
     SELECT *,
         ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY ReasonForLeaving DESC) AS EventOrder
     FROM PersonEvents
)
SELECT p.*, MostRecent.DateStarted AS MemberSince, NextRecent.ReasonForLeaving AS ReasonForChange
FROM Person p
     LEFT OUTER JOIN SortedEvents AS MostRecent ON p.Id = MostRecent.PersonId AND MostRecent.EventOrder = 1
     LEFT OUTER JOIN SortedEvents AS NextRecent ON p.Id = NextRecent.PersonId AND NextRecent.EventOrder = 2

which provides the nicely formatted output:

Id          Name   MemberSince              ReasonForChange
1           Iain   2013-02-12 00:00:00.000  holiday
2           Fred   2013-06-12 00:00:00.000  had enough
3           Mary   2013-09-12 00:00:00.000  pregnant
4           Foo    2012-10-12 00:00:00.000  NULL
5           Bar    NULL                     NULL

in reality you could pick multiple columns from any row number. The real life example (again, student enrolment history) picks:

- From the master student table:

- student id

- name

- DOB, etc

- From the Enrolment History table as "current enrolment"

- School id

- various enrolment status info

- date started

- From the Enrolment History table as "previous enrolment"

- reason for leaving

This method is quite efficient with about 150k students and their respective history.

complete SQL for my tests:

CREATE TABLE Person
(
     Id INT NOT NULL,
     Name VARCHAR(50)
)
GO
CREATE TABLE PersonEvents
(
     PersonId INT NOT NULL,
     DateStarted DATETIME NOT NULL,
     ReasonForLeaving VARCHAR(50)
)
GO
INSERT INTO Person
     SELECT 1, 'Iain' UNION ALL
     SELECT 2, 'Fred' UNION ALL
     SELECT 3, 'Mary' UNION ALL
     SELECT 4, 'Foo'  UNION ALL
     SELECT 5, 'Bar'
GO
INSERT INTO PersonEvents
     SELECT 1, '20110312', 'sick'       UNION ALL
     SELECT 1, '20130212', NULL         UNION ALL
     SELECT 1, '20120412', 'holiday'    UNION ALL
     SELECT 2, '20110512', 'new baby'   UNION ALL
     SELECT 2, '20130612', NULL         UNION ALL
     SELECT 2, '20120712', 'had enough' UNION ALL
     SELECT 3, '20110812', 'pregnant'   UNION ALL
     SELECT 3, '20130912', NULL         UNION ALL
     SELECT 4, '20121012', NULL
GO

--SELECT *
--FROM Person
--SELECT *
--FROM PersonEvents
--GO
WITH SortedEvents AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY DateStarted DESC) AS EventOrder
    FROM PersonEvents
)
SELECT p.*, MostRecent.DateStarted AS MemberSince, NextRecent.ReasonForLeaving AS ReasonForChange
FROM Person p
    LEFT OUTER JOIN SortedEvents AS MostRecent ON p.Id = MostRecent.PersonId AND MostRecent.EventOrder = 1
    LEFT OUTER JOIN SortedEvents AS NextRecent ON p.Id = NextRecent.PersonId AND NextRecent.EventOrder = 2
GO

SELECT p.*,
    (
        SELECT TOP 1 DateStarted
        FROM PersonEvents pe
        WHERE pe.PersonId = p.Id
        ORDER BY DateStarted DESC
    ) AS MemberSince,
    'unknown' AS ReasonForChange
FROM Person p
GO

DROP TABLE Person
DROP TABLE PersonEvents
GO

Problem

I have the need to do multiple joins with the same table, between (eg) Person and PersonEvents. There are multiple events for each person (0 or more). I need to create a VIEW that selects each person with certain columns from their most recent event, plus columns from the next-most-recent event. Person data: ``` Id Name 1 Iain 2 Fred 3 Mary 4 Foo 5 Bar ``` PersonEvents data: ``` PersonId DateStarted ReasonForLeaving 1 2011-03-12 00:00:00.000 sick 1 2013-02-12 00:00:00.000 NULL 1 2012-04-12 00:00:00.000 holiday 2 2011-05-12 00:00:00.000 new baby 2 2013-06-12 00:00:00.000 NULL 2 2012-07-12 00:00:00.000 had enough 3 2011-08-12 00:00:00.000 pregnant 3 2013-09-12 00:00:00.000 NULL 4 2012-10-12 00:00:00.000 NULL ``` An output sample would be: ``` Id Name MemberSince ReasonForChange 1 Iain 2011-03-12 00:00:00.000 holiday 4 Foo 2012-10-12 00:00:00.000 NULL ... ``` The "old way" used a top 1 join or sub-select statement: ``` SELECT p.*, ( SELECT TOP 1 DateStarted FROM PersonEvents e WHERE e.PersonId = p.Id ORDER BY DateFoo DESC ) As MemberSince FROM Person p .... ``` However if you need multiple columns from this Join, (eg Date, Comment, and maybe further ids), then you need to do multiple sub-select statements, which is expensive. So the question is: How do you get multiple columns from a join using the row number for the most recent, and previous events?

Original source