SQL ORDER BY and LIMIT: Sorting, Top-N and Pagination

Rows in a table have no order. That is not a detail — it is the whole reason ORDER BY exists. A query without it returns rows in whatever sequence was cheapest for the database to produce, which may look sorted today and come back shuffled tomorrow after an index changes.

This page covers how sorting actually works, then pairs it with LIMIT to answer the two questions that follow: give me the top few, and give me page four. Everything runs against the sample database.

Sorting one column

Ascending is the default, so these two are the same query:

SELECT first_name, salary
FROM Employees
ORDER BY salary;

Add DESC for highest first, which is what you want far more often in reports:

SELECT first_name, salary
FROM Employees
ORDER BY salary DESC;

ASC and DESC attach to each column separately, not to the whole clause — a point that matters as soon as you sort by more than one thing.

Sorting by several columns

The second column breaks ties in the first, the third breaks ties in the second, and so on:

SELECT first_name, dept_id, salary
FROM Employees
ORDER BY dept_id ASC, salary DESC;

Departments in ascending order; within each department, the best paid first. Swap the two and you get a completely different report from the same data — the order of the columns in ORDER BY is the order of priority.

Sorting by an expression or an alias

You can sort by anything you can compute, whether or not it is in the SELECT list:

SELECT first_name, salary
FROM Employees
ORDER BY salary * 12 DESC;

And unlike WHERE, ORDER BY can use a column alias, because it runs after the SELECT list has been evaluated:

SELECT first_name, salary * 12 AS annual_pay
FROM Employees
ORDER BY annual_pay DESC;

That asymmetry surprises people: WHERE annual_pay > 1000000 fails, while ORDER BY annual_pay works. Both follow from the clause order — WHERE is evaluated before SELECT, ORDER BY after it.

Sorting by column position also works, and is best avoided:

SELECT first_name, dept_id
FROM Employees
ORDER BY 2, 1;

ORDER BY 2, 1 means “by the second column, then the first”. It saves typing and breaks silently the moment somebody reorders the SELECT list, which is why most style guides ban it.

Where NULLs end up

NULL is not a value, so it has no natural place in a sort order — and the standard leaves the choice to the database:

SELECT first_name, manager_id
FROM Employees
ORDER BY manager_id;

If the placement matters, do not rely on the default. The portable trick is to sort by a flag first:

SELECT first_name, manager_id
FROM Employees
ORDER BY CASE WHEN manager_id IS NULL THEN 1 ELSE 0 END,
         manager_id;

That puts the rows with a value first and the NULLs after them on every database, because you have made the rule explicit instead of inheriting one.

LIMIT: the top few rows

Sort, then take the first n. This is the whole top-N pattern:

SELECT first_name, salary
FROM Employees
ORDER BY salary DESC
LIMIT 3;

OFFSET and pagination

OFFSET skips rows before LIMIT starts counting, which is how a page of results is fetched:

SELECT first_name, salary
FROM Employees
ORDER BY salary DESC, emp_id
LIMIT 4 OFFSET 4;

Rows five to eight — page two, at four rows per page. The general form is LIMIT page_size OFFSET (page_number - 1) * page_size. Note the emp_id at the end of the ORDER BY: without a unique tie-breaker, a row can appear on two pages or on none, because the database is free to order tied rows differently on each query.

The same mechanism answers “the second highest salary” — skip one, take one — which is covered in full on the second highest salary page.

The syntax is different on every database

Row limiting is the least portable part of everyday SQL:

The engine in this playground accepts both LIMIT and TOP:

SELECT TOP 3 first_name, salary
FROM Employees
ORDER BY salary DESC;

The dialect differences page lists these side by side along with the date and string functions, which are the other places portable SQL stops being portable.

Practice this topic

Reading explains the idea; producing it yourself is what makes it stick. These exercises use exactly what this page covers, and your answer is checked by running it against the same sample database:

• Biggest departments firstMedium
• Big spendersHard
• Orders by monthMedium
• Look at the whole product catalogueEasy

See all 42 exercises →

Frequently asked questions

Related reading

• The SELECT statement: a beginner’s guide
• Find the second highest salary in SQL
• SELECT DISTINCT: removing duplicate rows from results
• Row limiting and other dialect differences
• Window functions: ranking without losing rows