Getting Started

Sorting and limiting in SQL

Topic 4 of 45 · 9 minFree lesson

A database doesn't promise to give you rows in any order. Often they come back in the order they were added, but you can't count on it. ORDER BY sorts the rows by a column you choose, and LIMIT keeps only the first few. Together they answer questions like "what are our three most expensive products?"

Sorting with ORDER BY

Put ORDER BY and a column name at the end of the query. The rows come back sorted by that column, from smallest to biggest.

Example 1

SELECT name, priceFROM productsORDER BY price;

Smallest first is called ascending order. It's what you get unless you ask for something else. You can write ASC after the column to make it clear, but you don't have to.

Biggest first with DESC

Add DESC after the column to sort the other way. Descending order puts the biggest value first. For dates, that means the newest first.

Example 2

SELECT name, joinedFROM customersORDER BY joined DESC;

Text sorts in alphabetical order, so ORDER BY name goes from A to Z, and ORDER BY name DESC from Z to A.

NoteTwo customers have no city. If you sort by city, those empty values come last, or first with DESC. You'll meet missing values properly in a later topic.

Sorting by several columns

List more than one column, with commas between them. Rows are sorted by the first column. When two rows tie on it, the second column decides.

Example 3

SELECT status, ordered, idFROM ordersORDER BY status, ordered DESC;

status | ordered | id-----------+------------+---- cancelled | 2025-02-20 | 5 delivered | 2025-03-09 | 7 delivered | 2025-03-01 | 6 delivered | 2025-02-14 | 4 delivered | 2025-02-03 | 3 delivered | 2025-01-12 | 2 delivered | 2025-01-05 | 1 pending | 2025-03-28 | 12 pending | 2025-03-27 | 11 pending | 2025-03-25 | 10 shipped | 2025-03-18 | 9 shipped | 2025-03-15 | 8(12 rows)

The orders are grouped by status, from A to Z. Within each status, the newest order comes first. Each column gets its own ASC or DESC.

Keeping the first few with LIMIT

LIMIT goes at the very end and says how many rows you want. After sorting by price from highest to lowest, LIMIT 3 keeps the three most expensive products.

Example 4

SELECT name, priceFROM productsORDER BY price DESCLIMIT 3;

name | price------------+------- Olive Tree | 89.00 Bonsai | 49.00 Monstera | 35.00(3 rows)

Some other databases write this differently. SQL Server, for example, uses SELECT TOP 3 instead of LIMIT 3.

Pages of results with OFFSET

OFFSET skips a number of rows before LIMIT starts counting. Shops use this for pages: the first page shows rows 1 to 3, and OFFSET 3 starts the next page at row 4.

Example 5

SELECT name, priceFROM productsORDER BY price DESCLIMIT 3 OFFSET 3;

name | price--------------+------- Snake Plant | 22.00 Watering Can | 15.00 Fern | 12.50(3 rows)

These are the fourth, fifth and sixth most expensive products. For the third page, you'd write OFFSET 6.

Filtering and sorting together

WHERE and ORDER BY work well together. The order of the parts matters: SELECT, FROM, WHERE, then ORDER BY, then LIMIT.

Example 6

SELECT name, priceFROM productsWHERE category = 'herbs'ORDER BY price;

name | price----------+------- Mint | 4.00 Basil | 4.50 Rosemary | 6.00(3 rows)

WHERE picks the rows first, then ORDER BY sorts the ones that are left.

Common mistakeWriting the parts of a query in the wrong order. ORDER BY must come after WHERE, so PostgreSQL stops at the word it didn't expect.

Example 7

SELECT name, priceFROM productsORDER BY priceWHERE category = 'tools';

ERROR: syntax error at or near "WHERE"

Move the WHERE line above ORDER BY. The parts always go SELECT, FROM, WHERE, ORDER BY, LIMIT.

TipAlways use ORDER BY with LIMIT. Without it, "the first three rows" could be any three rows, and the answer might change from one day to the next.

Try it yourself

Run the code in the editor and answer 8 practice questions on sorting and limiting. It's free, you only need an account.

Practice this topic free I have an account