CODEWITHSUNDEEP
sqlsql-server
Lukas
Lukas

Reputation: 2243

OFFSET doesn't work when a column is sorted with all NULLs

I don't understand why the OFFSET clause doesn't work when I'm retrieving a table sorted by a column that contains all NULL values?

For example, let's say I have a table People with ID, Gender, and Race columns. The Race column contains all NULL values.

When I run these to queries, why do they retrieve the same data?

SELECT * 
FROM People
ORDER BY Race ASC
    OFFSET 0 ROWS --offset 0 rows
    FETCH NEXT 15 ROWS ONLY

SELECT * 
FROM People
ORDER BY Race ASC
    OFFSET 15 ROWS --offset 15 rows
    FETCH NEXT 15 ROWS ONLY

I understand ordering is not functioning, since all values in Race column are NULL. I don't understand why it doesn't offset 15 rows in the second query. OFFSET functions properly only if the column contains some non-NULL values.

Upvotes: 1

Views: 1547

Answers (2)

lptr
lptr

Reputation: 6798

SELECT * 
FROM People
ORDER BY Race ASC, row_number() over(order by Race)
--    OFFSET 0 ROWS --offset 0 rows
--    FETCH NEXT 15 ROWS ONLY
    OFFSET 15 ROWS --offset 15 rows
    FETCH NEXT 15 ROWS ONLY

Upvotes: 1

zealous
zealous

Reputation: 7503

If Race is not unique then the order in that group is not guaranteed to repeat. Add ID to the sort to get a repeatable sort.

SELECT * 
FROM People
ORDER BY Race, ID
OFFSET 0 ROWS
FETCH NEXT 15 
ROWS ONLY

SELECT * 
FROM People
ORDER BY Race, ID
OFFSET 15 ROWS
FETCH NEXT 15 
ROWS ONLY

Upvotes: 1

Related Questions