How do ORDER BY, LIMIT, OFFSET and FETCH FIRST work across databases, and why is keyset pagination better than OFFSET?
How do ORDER BY, LIMIT, OFFSET and FETCH FIRST work across databases, and why is keyset pagination better than OFFSET?
ORDER BY sorts the result. Without it, row order is undefined even if it looks stable today. Limiting rows is where dialects differ.
| Database | Take first 10 | Skip 20, take 10 |
|---|---|---|
| PostgreSQL, MySQL, SQLite | LIMIT 10 |
LIMIT 10 OFFSET 20 |
SQL standard, PostgreSQL 13+ (WITH TIES), Oracle 12c+, SQL Server 2012+ |
FETCH FIRST 10 ROWS ONLY |
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY |
| SQL Server | SELECT TOP (10) ... |
OFFSET ... FETCH (needs ORDER BY) |
FETCH FIRST ... WITH TIES also returns rows tied with the last one. MySQL has no FETCH FIRST.
SELECT id, title, created_at
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.