LIMIT and OFFSET: Fewer Rows
The LIMIT clause controls how many rows your query returns.
It is useful when you want to:
- See just a few rows
- Show only the top results
- Show results one page at a time
Syntax
SELECT column1, column2, ... FROM table_name
LIMIT number_of_rows;
Example 1: Return only the first 3 employees
SELECT name, age FROM employees
LIMIT 3;
Output:
name | age
-------+-----
Alice | 28
Bob | 32
John | 25
(3 rows)
This returns only 3 rows.
Example 2: Combine with ORDER BY (Top N)
SELECT name, age FROM employees
ORDER BY age DESC, name
LIMIT 3;
Output:
name | age
--------+-----
Jack | 33
Thomas | 33
Bob | 32
(3 rows)
This returns the 3 oldest employees. It sorts by age from oldest to youngest first, and then keeps 3 rows. name breaks the tie between employees of the same age.
Example 3: Skip Rows with OFFSET
OFFSET skips some rows before LIMIT starts counting. This is how you show the second page of results:
SELECT name, age FROM employees
ORDER BY age DESC, name
LIMIT 3 OFFSET 3;
Output:
name | age
-------+-----
Paul | 32
Grace | 31
Tina | 31
(3 rows)
This skips the first 3 rows and returns the next 3.
Tip
- Without
ORDER BY,LIMITreturns any N rows. The order is not promised. - With
ORDER BY,LIMITgives you "top N" results.
In the exercise, find the two youngest employees.