Narcis Miclaus

8 min read

SQL Server, from the ground up Lesson 8 / 40

ORDER BY, TOP, and OFFSET/FETCH

Sorting, paging, TIES, stable sorts, and the one thing everyone gets wrong when they try 'top N rows per group' for the first time.

ORDER BY looks innocent. Pick a column, pick a direction, done. Then you write a pagination endpoint, ship it, and a week later the customer success team asks why the same order keeps showing up on page 1 and page 2 of the admin dashboard. Nothing is broken. Sort order just doesn’t do what you think when there are ties.

This lesson is about ORDER BY, TOP, and OFFSET ... FETCH — how they interact, when they bite, and the one pattern every SQL developer gets wrong the first time they need “top N rows per group.”

ORDER BY: the basics

ORDER BY sorts the result set. Ascending by default, descending with DESC:

SELECT OrderId, Total, OrderDate
FROM Sales.Orders
ORDER BY OrderDate DESC;     -- newest first

Multiple columns, applied left to right:

SELECT OrderId, CountryCode, Total, OrderDate
FROM Sales.Orders
ORDER BY CountryCode ASC,    -- group by country
         Total      DESC;    -- inside each country, biggest first

You can sort by any column from the source tables — even if it’s not in the SELECT list — because ORDER BY runs after SELECT in the logical order (lesson 6) and has access to everything FROM produced.

ORDER BY in subqueries: mostly pointless

A very common mistake in SQL Server: writing ORDER BY in a subquery, view, or CTE and expecting the outer query to preserve the order.

-- This does NOT guarantee that the outer SELECT returns rows in date order.
SELECT *
FROM (
    SELECT OrderId, OrderDate
    FROM Sales.Orders
    ORDER BY OrderDate DESC     -- ← useless here
) AS o;

The SQL standard says a table expression has no inherent order. The sort inside the subquery is allowed to be ignored. SQL Server will typically allow you to do this only when combined with TOP, and the ordering of the outer query is still undefined. Put ORDER BY only on the outermost query you’re going to consume.

If you’re writing a view or a CTE, don’t bother sorting inside. Sort at the final consumer.

The “ties” problem nobody warns you about

Here’s a query that looks obviously correct:

-- 10 most recent orders
SELECT TOP (10) OrderId, OrderDate
FROM Sales.Orders
ORDER BY OrderDate DESC;

Say your data has 20 orders, five of which were placed in the exact same second (CSV import at midnight, for example). Those five rows have identical OrderDate. They’re tied.

When there’s a tie, SQL Server picks an order that’s unspecified — essentially the order it happens to produce. Nothing guarantees that rerunning the query gives you the same order. Nothing guarantees it matches the ordering your colleague sees on their machine. Nothing guarantees it matches what came out of yesterday’s nightly job.

For pagination, this is catastrophic. “Page 1 shows orders A B C D E. Click next. Page 2 shows orders D E F G H.” D and E appeared twice because the sort wasn’t deterministic across calls.

The fix: always add a tiebreaker — ideally the primary key.

SELECT TOP (10) OrderId, OrderDate
FROM Sales.Orders
ORDER BY OrderDate DESC,
         OrderId   DESC;    -- ← ties broken deterministically

OrderId is the clustered primary key, so every row has a unique value. The sort is now totally ordered: no two rows compare equal, so the output is reproducible across runs.

Make “include the primary key in ORDER BY” a reflex. The day you ship a pagination endpoint without it is the day support gets a bug report about duplicate rows.

TOP with a value or a variable

TOP limits the rows returned:

SELECT TOP (10) ...          -- always wrap in parens for clarity
SELECT TOP 10 ...            -- old syntax, also works
SELECT TOP (@n) ...           -- parameterized; requires parens
SELECT TOP (10) WITH TIES ...  -- include ties on the last row

WITH TIES is a hidden gem. Consider this: “show the top 3 biggest orders in Italy,” but if the 3rd and 4th orders have the same Total, include both.

SELECT TOP (3) WITH TIES
       OrderId, Total, CountryCode
FROM Sales.Orders
WHERE CountryCode = 'IT'
ORDER BY Total DESC;

If three rows have the top-3 totals and a fourth row ties the 3rd, you get 4 rows back. Very useful for leaderboard-style queries where you don’t want to arbitrarily cut off equal-ranked items.

TOP without ORDER BY is a coin flip

SELECT TOP (10) OrderId FROM Sales.Orders;

Returns some 10 rows. Could be the first 10 by insertion order. Could be ten random ones from different pages. Not guaranteed. Don’t do this unless you’re genuinely sampling for exploration.

OFFSET ... FETCH: paging the modern way

The SQL-standard paging syntax, in SQL Server since 2012:

-- Page 3, 10 rows per page
SELECT OrderId, OrderDate
FROM Sales.Orders
ORDER BY OrderDate DESC, OrderId DESC
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

OFFSET N ROWS — skip the first N rows. FETCH NEXT M ROWS ONLY — return the next M. Requires ORDER BY. Straightforward, and it’s the right choice for admin UIs, list views, and any paged dashboard.

Calling pattern from an application:

-- Parameterized, page-size is fixed at 25
DECLARE @page INT = 3;
DECLARE @pageSize INT = 25;

SELECT OrderId, OrderDate
FROM Sales.Orders
ORDER BY OrderDate DESC, OrderId DESC
OFFSET (@page - 1) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;

Performance note: OFFSET is not free. The engine still has to walk through the first N rows to throw them away, then return the next M. For page 1000 of a million-row table, the engine reads 10,000 rows worth of index pages, which is slow.

For very deep pagination on hot tables, keyset pagination (“show me the next 25 orders after the one with OrderId 12345”) is faster than OFFSET:

-- Keyset pagination: remember the last row from the previous page
DECLARE @lastOrderDate DATETIME2(0) = '2026-03-15 10:22:00';
DECLARE @lastOrderId  BIGINT = 4821;

SELECT TOP (25) OrderId, OrderDate
FROM Sales.Orders
WHERE (OrderDate < @lastOrderDate)
   OR (OrderDate = @lastOrderDate AND OrderId < @lastOrderId)
ORDER BY OrderDate DESC, OrderId DESC;

This uses the index directly — no “skip N and start counting” overhead. Pass the last row’s OrderDate and OrderId to the next call. Works beautifully for infinite-scroll UIs and well-paginated APIs. For the admin dashboard where someone clicks “page 7,” OFFSET is still fine.

The “top N per group” mistake

Very common real request at Runehold: “give me the 3 biggest orders per country.”

The intuitive-but-wrong query:

-- THIS DOES NOT WORK LIKE YOU THINK
SELECT TOP (3) OrderId, CountryCode, Total
FROM Sales.Orders
ORDER BY CountryCode, Total DESC;

This returns 3 rows total, not 3 per country. TOP limits the whole result set, not groups.

The right answer uses a window function — ROW_NUMBER() partitioned by group. Full details are lesson 12, but here’s the pattern:

SELECT OrderId, CountryCode, Total
FROM (
    SELECT OrderId,
           CountryCode,
           Total,
           ROW_NUMBER() OVER (
               PARTITION BY CountryCode
               ORDER BY Total DESC, OrderId DESC
           ) AS rn
    FROM Sales.Orders
) AS ranked
WHERE rn <= 3
ORDER BY CountryCode, Total DESC;

ROW_NUMBER() OVER (PARTITION BY CountryCode ORDER BY Total DESC) assigns each row a rank within its country. The outer query keeps ranks 1, 2, 3. Exactly 3 rows per country.

This is one of the most useful patterns in practical SQL. Memorize the shape. We’ll unpack windows properly in lesson 12.

Collation and sort order for text

Text sort order depends on the collation of the column or database (lesson 4). A case-insensitive accent-sensitive collation (the SQL Server default in Western Europe) treats 'anne' and 'ANNE' as equal for sorting purposes but 'café' and 'cafe' as different.

Runehold’s customer support runs reports like “top customers sorted alphabetically by name.” In a multi-locale database, this sort order is a business decision:

  • Case-insensitive: 'Anne' and 'anne' sort together.
  • Accent-sensitive: 'Bérénice' sorts after 'Bernice'.
  • Locale-specific: German 'ß' sorts with 'ss' in one collation, after 's' in another.

You can force a specific collation on a single query:

SELECT Name
FROM Sales.Customer
ORDER BY Name COLLATE Latin1_General_100_CI_AI;  -- accent-insensitive too

If international sort order matters for your team (it often does in an EU company like Runehold), agree on one collation, document it, stick with it.

Run this on your own machine

USE Runehold;
GO

-- Add a few more orders so sorting has interesting ties
INSERT INTO Sales.Orders (CustomerId, OrderDate, Total, CountryCode, VatRate)
VALUES (1, '2026-04-01 10:00:00',  19.00, 'NL', 0.2100),
       (2, '2026-04-01 10:00:00',  19.00, 'IT', 0.2200),   -- same date, same total
       (3, '2026-04-01 10:00:00', 129.00, 'DE', 0.1900),
       (4, '2026-04-05 14:30:00',  75.00, 'RO', 0.1900);

-- Query 1: non-deterministic TOP
-- Run it 5 times; the 10 rows may come back in different orders.
SELECT TOP (5) OrderId, OrderDate, Total
FROM Sales.Orders
ORDER BY OrderDate DESC;

-- Query 2: same, with a tiebreaker — always reproducible
SELECT TOP (5) OrderId, OrderDate, Total
FROM Sales.Orders
ORDER BY OrderDate DESC, OrderId DESC;

-- Query 3: OFFSET-based paging
DECLARE @page INT = 1;
DECLARE @pageSize INT = 3;

SELECT OrderId, OrderDate, Total
FROM Sales.Orders
ORDER BY OrderDate DESC, OrderId DESC
OFFSET (@page - 1) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;

-- Query 4: TOP WITH TIES
SELECT TOP (2) WITH TIES
       OrderId, CountryCode, Total
FROM Sales.Orders
ORDER BY Total DESC;
-- If 2nd and 3rd both have the same Total, you get 3 rows.

-- Query 5: top-2 orders per country (preview of lesson 12)
SELECT OrderId, CountryCode, Total
FROM (
    SELECT OrderId, CountryCode, Total,
           ROW_NUMBER() OVER (
               PARTITION BY CountryCode
               ORDER BY Total DESC, OrderId DESC
           ) AS rn
    FROM Sales.Orders
) AS ranked
WHERE rn <= 2
ORDER BY CountryCode, Total DESC;

Run each query. Predict the row count before you look. Notice how the tiebreaker version in query 2 is reproducible while query 1 may not be.

Module 1 of the course is done. You can now create tables, pick data types, write SELECTs, filter with WHERE, handle NULLs, and sort/page correctly. That’s more than enough to be productive in most data-engineering jobs.

Module 2 starts next lesson: joins. Arguably the single most important SQL topic. We’ll cover INNER, LEFT, RIGHT, FULL, CROSS, and the Venn-diagram explanation that’s technically wrong but useful anyway.

Search