Sorting and paging: ORDER BY, TOP, OFFSET and DISTINCT

A table has no built-in order, and neither does a query result, unless you ask for one. Rows often come back in the order they were inserted, which makes it easy to believe that order is guaranteed. It is not: add an index, or let the table grow until SQL Server reads it in parallel, and the order changes. Only ORDER BY guarantees the order of a result.

ORDER BY

ORDER BY sorts by one or more columns, each ASC (smallest first, the default) or DESC (largest first). With several columns, the second sorts only rows that tie on the first:

1SELECT Name, CategoryId, Price2FROM dbo.Products3WHERE CategoryId IN (1, 2, 3)4ORDER BY CategoryId, Price DESC;

Name                 CategoryId  Price
-------------------  ----------  -----
Cordless drill                1  89.00
Claw hammer                   1  18.50
Tape measure 8 m              1   9.75
Cement 25 kg                  2   7.20
Sand 25 kg                    2   4.10
Plywood sheet 18 mm           3  42.00
Timber 2x4 2.4 m              3   6.80

ORDER BY is processed after SELECT, so it can use aliases and expressions, and even columns that are not in the SELECT list. NULL counts as smaller than any value, so NULLs come first in ascending order:

1SELECT FirstName, City, JoinedOn2FROM dbo.Customers3ORDER BY City, JoinedOn DESC;

FirstName  City       JoinedOn
---------  ---------  ----------
Ella       NULL       2023-09-14
Dev        Bradford   2023-08-23
Zoë        Harrogate  2024-02-15
Grace      Leeds      2024-01-08
Chloe      Leeds      2023-06-07
Asha       Leeds      2023-03-02
Farid      York       2023-11-30
Ben        York       2023-05-19

TOP, WITH TIES and OFFSET … FETCH

Sort, then choose how many rows to return. Turn on WITH TIES, switch to paging, and watch what happens when the cut falls between two rows with the same price:

1SELECT TOP (3) ProductId, Name, Price, Stock, Unit, AddedOn2FROM dbo.Products3ORDER BY Price;

TOP (n) returns the first n rows of the sorted result. Two products cost 6.80, so “the three cheapest” is ambiguous. WITH TIES settles it by including every row that ties with the last one:

1SELECT TOP (3) WITH TIES Name, Price2FROM dbo.Products3ORDER BY Price;

Name              Price
----------------  -----
Sand 25 kg         4.10
Wall plugs (100)   4.50
Timber 2x4 2.4 m   6.80
Safety goggles     6.80

For pages of results, as on a web page with Next and Previous buttons, use OFFSET … FETCH. It goes after ORDER BY and needs it. Here is page 2, four products per page:

1SELECT ProductId, Name, Price2FROM dbo.Products3ORDER BY Price, ProductId4OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

ProductId  Name               Price
---------  -----------------  -----
        4  Cement 25 kg        7.20
        3  Tape measure 8 m    9.75
        8  Wood screws (200)  11.90
       11  Paint roller set   12.50

DISTINCT

SELECT DISTINCT removes duplicate rows from the result. Rows count as duplicates only if every column in the SELECT list matches, and DISTINCT treats NULLs as equal to each other:

1SELECT DISTINCT City2FROM dbo.Customers3ORDER BY City;

City
---------
NULL
Bradford
Harrogate
Leeds
York

1SELECT DISTINCT CategoryId, Unit2FROM dbo.Products3WHERE CategoryId <= 34ORDER BY CategoryId;

CategoryId  Unit
----------  -----
         1  each
         2  bag
         3  each
         3  sheet

The full logical order

1. FROM: the source rows
2. WHERE: filter rows
3. SELECT: work out columns and aliases
4. DISTINCT: remove duplicate rows
5. ORDER BY: sort
6. TOP / OFFSET … FETCH: keep only some of the sorted rows

This is why TOP without ORDER BY is a gamble: it takes the first rows of an unsorted result, which can be any rows. The Grouping module adds GROUP BY and HAVING between WHERE and SELECT.

1SELECT TOP (3) Name, Price, Stock2FROM dbo.Products3WHERE Stock > 04ORDER BY Price, Name;

Name              Price  Stock
----------------  -----  -----
Sand 25 kg         4.10    220
Timber 2x4 2.4 m   6.80    500
Cement 25 kg       7.20    300