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, LIMIT returns any N rows. The order is not promised.
  • With ORDER BY, LIMIT gives you "top N" results.

In the exercise, find the two youngest employees.