Order by boolean column produce duplicate rows when paging in sql server

Question

I work on this table :

When do paging using the IsAdmin column, the result will contain duplicated rows. This is my query :

SELECT [UserId],[IsAdmin],[Date] FROM [Groups].[GroupMembers]
WHERE ([GroupId] = 5)
ORDER BY [IsAdmin] DESC
OFFSET 0 ROWS FETCH NEXT 6 ROWS ONLY 

And the second query :

SELECT [UserId],[IsAdmin],[Date] FROM [Groups].[GroupMembers]
WHERE ([GroupId] =5)    
ORDER BY [IsAdmin] DESC
OFFSET 6 ROWS FETCH NEXT 6 ROWS ONLY

The result for the two queries is :

But if user the Date column, I get result without duplicate.

My question is that why using boolean produce duplicate rows ?

Answer 1

The problem is that your order by is non-deterministic. As there are a lot of rows where Is Admin = 0, you aren't guaranteed that the same rows will be selected each time.

If you change your query to be

SELECT [UserId],[IsAdmin],[Date] FROM [Groups].[GroupMembers]
WHERE ([GroupId] = 5)
ORDER BY [IsAdmin] DESC, [UserId]

then you won't get the same row appearing twice (assuming that [UserId] is unique).

Actually, this still won't guarantee that a row won't appear twice. If rows are inserted between calls, and the new rows would have appeared on the current page, then some of the rows actually shown on the current page will be re-shown on the next page as they have been bumped down the order.