ORDER BY and Pagination — Sorted Results and OFFSET/FETCH

Sorting and pagination are fundamental to any API that returns lists. SQL Server’s OFFSET/FETCH NEXT syntax implements server-side pagination — only the requested page of rows is returned, not the entire table. This is what EF Core’s .Skip().Take() generates under the hood. Correct pagination requires a deterministic ORDER BY — without it, the same query can return different rows on different executions because SQL Server can return rows in any order.

ORDER BY and OFFSET/FETCH Pagination #

-- ── Basic ORDER BY ─────────────────────────────────────────────────────────
SELECT Id, Title, ViewCount, PublishedAt
FROM   dbo.Posts
WHERE  IsPublished = 1
ORDER  BY PublishedAt DESC;    -- most recent first

-- ── Multi-column sort ──────────────────────────────────────────────────────
SELECT Id, Title, Status, ViewCount
FROM   dbo.Posts
ORDER  BY Status ASC,          -- first sort by status
          ViewCount DESC;      -- then by views (descending) within each status

-- ── NULL ordering (NULLs sort first in ASC, last in DESC by default) ──────
-- Posts with NULL PublishedAt appear FIRST with ASC:
SELECT Id, Title, PublishedAt FROM dbo.Posts ORDER BY PublishedAt ASC;
-- To put NULLs last:
ORDER BY CASE WHEN PublishedAt IS NULL THEN 1 ELSE 0 END, PublishedAt ASC;

-- ── OFFSET/FETCH NEXT — server-side pagination ────────────────────────────
-- Parameters: @Page (1-based), @PageSize
DECLARE @Page     INT = 2;
DECLARE @PageSize INT = 10;

SELECT
    p.Id,
    p.Title,
    p.Slug,
    p.ViewCount,
    p.PublishedAt,
    u.DisplayName AS AuthorName,
    COUNT(*) OVER () AS TotalCount  -- total rows WITHOUT paging (window function)
FROM   dbo.Posts p
JOIN   dbo.Users u ON u.Id = p.AuthorId
WHERE  p.IsPublished = 1
ORDER  BY p.PublishedAt DESC          -- ← ORDER BY required for OFFSET/FETCH
OFFSET (@Page - 1) * @PageSize ROWS   -- skip rows from previous pages
FETCH  NEXT @PageSize ROWS ONLY;      -- return only this page

-- For page 1: OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY  → rows 1-10
-- For page 2: OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY → rows 11-20
-- For page 3: OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY → rows 21-30

-- ── Separate total count query (alternative to COUNT(*) OVER()) ───────────
-- More readable; two separate queries instead of one with window function
SELECT COUNT(*) AS TotalCount
FROM   dbo.Posts
WHERE  IsPublished = 1;

-- ── TOP WITH TIES — return top N including ties ────────────────────────────
SELECT TOP 3 WITH TIES Id, Title, ViewCount
FROM   dbo.Posts
WHERE  IsPublished = 1
ORDER  BY ViewCount DESC;
-- If 3rd and 4th posts both have 500 views, all 4 are returned

Common Mistakes #

Mistake 1 — Non-deterministic ORDER BY for pagination (duplicate or missing rows across pages) #

❌ Wrong — ORDER BY PublishedAt DESC alone; two posts with same timestamp may swap between pages.

✅ Correct — ORDER BY PublishedAt DESC, Id DESC — the unique Id as tiebreaker ensures deterministic order.

Mistake 2 — OFFSET/FETCH without ORDER BY (syntax error) #

❌ Wrong — FETCH NEXT 10 ROWS ONLY without ORDER BY; SQL Server requires ORDER BY for OFFSET/FETCH.

✅ Correct — always include an ORDER BY clause before OFFSET.