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.

Could you provide some sample data and expect result? – D-Shih May 04 '18 at 00:49 — D-Shih, May 04 '18 at 00:49

D-Shih · Answer 1 · 2018-05-04T00:58:56.667

You need add condition LocationID = 308644 in where clause on subquery,otherwise first and second row maybe doesn't contain LocationID = 308644.

Then You use LEFT JOIN that will not match LocationID = 308644 so will be NULL row.

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 AND LocationID = 308644) 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

EDIT

You can let locations table LEFT JOIN on subquery.

SELECT LatestNote.notecode, 
       LatestNote.notedate, 
       SecondLatestNote.notecode AS [NoteCode2nd], 
       SecondLatestNote.notedate AS [NotDate2nd] 
FROM   (
        SELECT TOP 2 TopTwoNotes.locationid, 
                     TopTwoNotes.noteid, 
                     Row_number() 
                       OVER (ORDER BY TopTwoNotes.notedate DESC) AS RowNum 
        FROM   locations LOC 
               LEFT JOIN notes(nolock) TopTwoNotes
                       ON TopTwoNotes.locationid = LOC.locationid 
        WHERE  TopTwoNotes.notecode = 'NOTIFY' 
               AND TopTwoNotes.notedate BETWEEN @DateFrom AND @DateTo 
               AND LOC.locationid = 308644
        ORDER BY TopTwoNotes.notedate DESC
        ) AS TopTwoNotes 
       INNER JOIN notes AS LatestNote 
               ON LatestNote.noteid = TopTwoNotes.noteid 
                  AND TopTwoNotes.rownum = 1 
       INNER JOIN notes AS SecondLatestNote 
               ON SecondLatestNote.noteid = TopTwoNotes.noteid 
                  AND TopTwoNotes.rownum = 2

That's not going to work, as I am only using the 308644 in this example - this will return thousands of rows without the limit on location id, so I can't put that where in there... — Bob, May 04 '18 at 00:42
How about my edit answer? If that isn't your expect please provide some sample data and expect result. — D-Shih, May 04 '18 at 00:54
The TopTwoNotes join can't contain a locationid in the where, as it is part of a larger query that will bring back multiple rows of different locations. The LatestNote and SecondLatestNote joins will also have to be left as there may not be 2 or even 1 value returned... — Bob, May 04 '18 at 05:03

Gordon Linoff · Accepted Answer · 2018-05-10T01:26:22.347

0

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

edited May 10 '18 at 01:26

answered May 04 '18 at 02:59

Gordon Linoff

1,242,037
58
646
786

I had to change the syntax of the OFFSET line to add the word ROWS (OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY), but this is exactly what I was after. Thank you! – Bob May 10 '18 at 00:43

SQL Server same-row joins using rownum returns no values

2 Answers2