SQL Server same-row joins using rownum returns no values

Question

I am trying to return multiple record values in the same row, so I wanted to use multiple joins to the same table. I need the top two records to be displayed side-by-side. These joins become 'LatestNote' and 'SecondLatestNote', using the RowNumber to separate them.

Very simple example follows, but I get no results. What am I messing up here?

SQL with joins that returns no values:

DECLARE @DateFrom AS DATETIME = CONVERT(DateTime, '2017-01-01 00:00:00.000', 120)
DECLARE @DateTo AS DATETIME = CONVERT(DateTime, '2018-01-01 00:00:00.000', 120)

SELECT
    LatestNote.NoteCode,
    LatestNote.NoteDate,           
    SecondLatestNote.NoteCode AS [NoteCode2nd],
    SecondLatestNote.NoteDate AS [NotDate2nd]

FROM Locations LOC


LEFT JOIN (
            SELECT TOP 2 LocationID, NoteID, Row_Number() OVER (ORDER BY Notedate DESC) AS RowNum
            FROM Notes(nolock) 
            WHERE NoteCode = 'NOTIFY' 
            AND NoteDate BETWEEN @DateFrom AND @DateTo
            ) 
                AS TopTwoNotes ON TopTwoNotes.LocationID = LOC.LocationID 

                LEFT JOIN Notes AS LatestNote ON LatestNote.NoteID = TopTwoNotes.NoteID AND TopTwoNotes.RowNum = 1

                LEFT JOIN Notes AS SecondLatestNote ON SecondLatestNote.NoteID = TopTwoNotes.NoteID AND TopTwoNotes.RowNum = 2 

WHERE LOC.LocationID = 308644

The result is all NULL, with a row only being returned because I am using a LEFT JOIN. Why does this not work??

This SQL shows that the data is there, with the results below:

SELECT TOP 2 LocationID, NoteID, Row_Number() OVER (ORDER BY NoteDate DESC) AS RowNum
            FROM Notes(nolock) 
            WHERE NoteCode = 'NOTIFY' 
            AND NoteDate BETWEEN @DateFrom AND @DateTo
            AND LocationID = 308644 

LocationID  NoteID  RowNum
308644  10291348    1
308644  10130566    2


SELECT
    NoteID,
    NoteCode,
    NoteDate,
    LEN(CAST(Note AS VARCHAR(8000))) AS [NoteCharCount]
FROM
    Notes
WHERE
    LocationID = 308644
    AND NoteDate BETWEEN @DateFrom AND @DateTo


NoteID  NoteCode    NoteDate    NoteCharCount
10130566    NOTIFY  2017-11-08  50
10291348    NOTIFY  2017-12-13  66

I must be missing something - can I not join using the rownum like this?

Thanks for your help.

Answer 1

You can use apply for this problem:

SELECT n_latest.NoteCode, n_latest.NoteDate,           
       n_prev.NoteCode AS NoteCode2nd, n_prev.NoteDate AS NoteDate2nd
FROM Locations l OUTER APPLY
     (SELECT n.*
      FROM Notes n
      WHERE n.NoteCode = 'NOTIFY' AND
            n.NoteDate BETWEEN @DateFrom AND @DateTo
      ORDER BY n.NoteDate DESC
      OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY
     ) n_latest OUTER APPLY
     (SELECT n.*
      FROM Notes n
      WHERE n.NoteCode = 'NOTIFY' AND
            n.NoteDate BETWEEN @DateFrom AND @DateTo
      ORDER BY n.NoteDate DESC
      OFFSET 1 ROWS FETCH FIRST 1 ROW ONLY
     ) n_prev