Lesson 8 of 26 · see the practice database

Top rows with LIMIT

Often you want a few rows, not all of them: the five dearest products, the three newest orders, the first page of a long list. LIMIT cuts the result down to the number of rows you ask for.

The first few rows

SELECT * FROM order_items LIMIT 5;

The database returns five rows and stops. On a table of millions of rows, this is the polite way to take a first look.

Without an ORDER BY, though, "the first five" has no real meaning. You get whichever five the database reaches first.

Top N: ORDER BY with LIMIT

Sort, then cut. That gives the top or bottom of a list. LIMIT always goes last.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

The three dearest products. Turn the sort around for the cheapest.

SELECT name, price
FROM products
ORDER BY price
LIMIT 3;

The single newest order:

SELECT id, order_date, status
FROM orders
ORDER BY order_date DESC
LIMIT 1;

With a filter

WHERE chooses the rows, ORDER BY sorts them, LIMIT cuts. The parts are written in that order.

SELECT name, salary
FROM employees
WHERE department = 'Sales'
ORDER BY salary DESC
LIMIT 2;

The trouble with ties

Two people in Sales earn the same salary. If you ask for the top two salaries, which of the tied rows you get beyond the first is up to the database. LIMIT counts rows. It knows nothing about ties.

Add a second sort column to make the choice steady, as the last lesson advised. If you need "everyone who shares the top salary", LIMIT is the wrong tool. A subquery or a window function answers that question.

Skipping rows with OFFSET

OFFSET skips some rows before LIMIT starts counting. Together they show a long list a page at a time.

SELECT id, name
FROM products
ORDER BY id
LIMIT 5 OFFSET 0;

SELECT id, name
FROM products
ORDER BY id
LIMIT 5 OFFSET 5;

The first query is page one. The second skips five rows and shows page two. For page number p with n rows on a page, the offset is (p - 1) * n.

Paging only works with a steady ORDER BY. Without one, a row might appear on two pages or on none.

The second highest

A classic interview question asks for the second highest salary. One way is to sort, skip one and take one.

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

DISTINCT removes repeated salaries first, so that two people on the top salary do not push the same figure into second place. It is the subject of the next lesson.

The same thing in other databases

This is one of the places where the products differ most.

SQL Server puts TOP straight after SELECT:

SELECT TOP 3 name, price
FROM products
ORDER BY price DESC;

The standard form, which PostgreSQL, Oracle and recent SQL Server versions accept, reads:

SELECT name, price
FROM products
ORDER BY price DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

MySQL also has a short form, LIMIT 5, 5, where the first number is the offset. It is easy to misread, so prefer the OFFSET word.

Deep pages are slow

OFFSET 100000 makes the database find and throw away a hundred thousand rows before returning any. Big sites avoid it by remembering the last row shown and asking for "the next 20 rows after id 5310" with a WHERE. You do not need this now. It is enough to know that OFFSET is fine for small numbers and costly for huge ones.

The order of the parts

SELECT, FROM, WHERE, ORDER BY, LIMIT. You now know the full skeleton of a query that reads one table.

Try it yourself

SELECT name, stock FROM products ORDER BY stock DESC LIMIT 4;

SELECT name, joined FROM customers ORDER BY joined DESC LIMIT 2;

SELECT name, hired FROM employees ORDER BY hired LIMIT 1;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.