Div Div - 2 months ago 12
SQL Question

Inner join gives undesired result with row number in sql server

I've tried to do a pagination on data in using

ROW_NUMBER()


Here is my query:

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS Row,* FROM SpecificOrders)
AS EMP
inner join Users as c on EMP.UserID = c.UserID
inner join Users as u on EMP.CreatedBy = u.UserID
inner join SpecificOrderPayment as p on EMP.OrderID= p.OrderID

WHERE Row BETWEEN 0 AND 10


When I execute this query, I get output like following with :

Row | OrderID | UserID |
1 | | |
5 | | |
6 | | |
7 | | |
8 | | |
9 | | |
10 | | |


If I remove this
WHERE Row BETWEEN 0 AND 10
condition then it'll gives me all records

Here my question is why I get only 7 rows and why here 2,3 and 4 is missing in the row column.

Moreover, If i remove 3rd join query (
SpecificOrderPayment
)then it will give me proper result.

Answer

you've got OrderID that are null or blanks in SpecificOrders and they are sorting to the top - the approach isn't wrong otherwise, although there are other ways of doing it such as TOP 10..etc

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS Row,* FROM SpecificOrders 
                                                  WHERE RTRIM(COALESCE(OrderID, '')) <> '') 
AS EMP
    inner join Users as c on EMP.UserID = c.UserID
    inner join Users as u on EMP.CreatedBy = u.UserID       
    inner join SpecificOrderPayment as p on EMP.OrderID= p.OrderID

WHERE Row BETWEEN 0 AND 10