SQL ROW_NUMBER() ... between x and y breaks Inner join

Question

I have this inner join statement that works for the most part, but when I add custom paging the join returns a null set. Any suggestions will be appreciated.

This returns the results that I expect

SELECT [Id]
,[Title]
FROM 
(SELECT [Id]
,[Title]
,ROW_NUMBER() OVER(ORDER BY id) as RowNum from Art) as e
INNER JOIN [ArtCat] p ON e.Id = p.ArtId 
WHERE 
p.CatId = @CategoryNum

This returns a null set

SELECT [Id]
,[Title]
FROM 
(SELECT [Id]
,[Title]
,ROW_NUMBER() OVER(ORDER BY id) as RowNum from Art) as e
INNER JOIN [ArtCat] p ON e.Id = p.ArtId 
WHERE 
p.CatId = @CategoryNum
AND  RowNum BETWEEN @startIndex AND (@startIndex + @pageSize)

Thanks in advance!

Looks fine to me. I have run into strange things with windowed functions before though. If no one gives better answer, try putting results of first SQL into temp table and then run you RowNum BETWEEN .. from it — Steve, Sep 25 '13 at 21:24

score 0 · Answer 1 · answered Sep 25 '13 at 21:18

Formatting is a little hard to read, but the syntax is fine, why don't you check the bounds of your rownum:

SELECT MIN(RowNum),MAX(RowNum)          
FROM (SELECT [Id]
            ,[Title]
            ,ROW_NUMBER() OVER(ORDER BY id) as RowNum 
      FROM Art
      ) as e
INNER JOIN [ArtCat] p ON e.Id = p.ArtId 
WHERE p.CatId = @CategoryNum

Odds are you should be using the RowNum after limiting on p.CatId = @CategoryNum

Guessing this works:

SELECT [Id]
      ,[Title]
FROM (SELECT  [Id]
             ,[Title]
             ,ROW_NUMBER() OVER(ORDER BY id) as RowNum         
      FROM (SELECT [Id]
                  ,[Title]
            FROM Art
            ) as e
      INNER JOIN [ArtCat] p ON e.Id = p.ArtId 
      WHERE p.CatId = @CategoryNum
     )sub
WHERE RowNum BETWEEN @startIndex AND (@startIndex + @pageSize)

I had some one at work help me. You are right about putting the inner join in side the "from (select)" table. I needed to filter the results before i set the row numbers. — user2817020, Sep 26 '13 at 15:31

score 0 · Answer 2 · answered Sep 26 '13 at 15:42

Goat CO you where really helpful!! thanks. The code we ended up using was.

SELECT
       ROW_NUMBER() OVER(ORDER BY id) as e.RowNum  
       ,e.[Id]
      ,e.[Title]

FROM (SELECT  
          [Id]
         ,[Title]
         ,ROW_NUMBER() OVER(ORDER BY id) as RowNum         
  FROM 
     (SELECT
           ROW_NUMBER() OVER(ORDER BY id) as RowNum
           ,[Id]
           ,[Title]

           FROM Art
              INNER JOIN [ArtCat] p ON e.Id = p.ArtId 

       WHERE p.CatId = @CategoryNum
     ) as e
WHERE 
  e.RowNum BETWEEN @startIndex AND (@startIndex + @pageSize)

To prefilter the results (like you had) but to make another row on the outer table to really organize the results.

Also my coworker complained about my formatting Too.. Ha

SQL ROW_NUMBER() ... between x and y breaks Inner join

2 Answers2