ORDER BY, TOP and pagination

Reliable first pages require deterministic ordering.

UnderstandStudy an examplePractise

Understand the concept

ORDER BY sorts a result ASC or DESC. Include a unique tie-breaker when equal values occur, otherwise their relative order is unspecified. TOP without ORDER BY does not mean highest or newest. WITH TIES can also return rows tied with the last selected ordering value.

OFFSET/FETCH implements pagination, but large offsets can require skipping many rows. Keyset pagination continues from the last seen key. Concurrent inserts and changes to sort columns can affect the experience with either method.

WHEN TO USE IT

Use it for list screens, top values and stable pagination.

COMMON PITFALL

Expecting a stable TOP result without ORDER BY.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT TOP (3) ProductName, Price FROM lab.Product ORDER BY Price DESC, ProductId;

Laptop, Monitor and Desk are the three most expensive products.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

List the three cheapest products with a unique tie-breaker.

Show the solution and explanation

Worked solution

SELECT TOP (3) ProductName, Price FROM lab.Product ORDER BY Price, ProductId;

Notebook, Cable, Mouse. PostgreSQL/MySQL commonly use LIMIT instead.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.