---
title: "Understanding LIMIT in PostgreSQL (With Examples)"
description: "The PostgreSQL LIMIT clause limits the number of rows returned by a query. Learn why this is helpful and how you can use it."
section: "Postgres basics"
---

> **TimescaleDB is now Tiger Data.**

*Written by Team Timescale*

**

When working with large datasets in PostgreSQL (or TimescaleDB), limiting the number of rows returned by a query is often useful. This limitation can be particularly helpful for improving performance and managing the amount of data your application needs to handle at once. The `LIMIT` clause in PostgreSQL is designed to do just that.

In this post, we'll explore the `LIMIT` clause, understand its importance, and see how you can use it with practical examples.




## What Is the LIMIT Clause in PostgreSQL?

The `LIMIT` clause in PostgreSQL specifies the maximum number of rows that a query should return. It's commonly used for pagination, where results are divided into pages, or simply to fetch a subset of rows for analysis or display purposes.




### Syntax of the LIMIT clause

The basic syntax of the `LIMIT` clause is as follows:

`SELECT column1, column2, ...
FROM table
LIMIT number_of_rows;
`

Optionally, you can use the `OFFSET` clause to skip a specified number of rows before beginning to return rows:

`SELECT column1, column2, ...
FROM table
LIMIT number_of_rows OFFSET skip_rows;
`

### Example table

Let's consider a table `employees` with the following structure and data:

`CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC
);
`

`INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Engineering', 70000),
('Bob', 'HR', 50000),
('Carol', 'Engineering', 75000),
('Dave', 'Marketing', 60000),
('Eve', 'Engineering', 72000),
('Frank', 'HR', 52000),
('Grace', 'Marketing', 58000),
('Hank', 'Engineering', 69000),
('Ivy', 'HR', 51000),
('Jack', 'Marketing', 62000);
`

### Practical examples with LIMIT

Let's dive into some examples to see the `LIMIT` clause in action.


****

**Example 1: Limiting the number of rows returned**

Suppose we want to retrieve the first five employees from the `employees` table. We can use the `LIMIT` clause to achieve this:

`SELECT id, name, department, salary
FROM employees
LIMIT 5;
`

**Result:**

` id | name  | department | salary
----+-------+------------+--------
  1 | Alice | Engineering|  70000
  2 | Bob   | HR         |  50000
  3 | Carol | Engineering|  75000
  4 | Dave  | Marketing  |  60000
  5 | Eve   | Engineering|  72000
`

This query returns the first five rows from the `employees` table.




**Example 2: Using LIMIT with OFFSET
**Now, let's say we want to retrieve the next set of five employees, effectively paginating our results. We can use the `OFFSET` clause along with `LIMIT`:

`SELECT id, name, department, salary
FROM employees
LIMIT 5 OFFSET 5;
`


**Result:**

` id | name  | department | salary
----+-------+------------+--------
  6 | Frank | HR         |  52000
  7 | Grace | Marketing  |  58000
  8 | Hank  | Engineering|  69000
  9 | Ivy   | HR         |  51000
 10 | Jack  | Marketing  |  62000
`

This query skips the first five rows and returns the next five rows from the `employees` table.




**Example 3: Combining LIMIT with ORDER BY**


In many cases, you may want to order the results before limiting the number of rows. For instance, to get the top three highest-paid employees, you can combine `ORDER BY` with `LIMIT`:


`SELECT id, name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
`

**Result:**

` id | name  | department | salary
----+-------+------------+--------
  3 | Carol | Engineering|  75000
  5 | Eve   | Engineering|  72000
  1 | Alice | Engineering|  70000
`

This query orders the employees by their salary in descending order and returns the top three highest-paid employees.




## Conclusion

The `LIMIT` clause is a valuable tool in PostgreSQL for controlling the number of rows a query returns. Whether you're implementing pagination, fetching a subset of rows for display, or simply improving query performance, understanding and utilizing the `LIMIT` clause can significantly enhance your ability to manage and analyze data efficiently.


To learn more about how to use this clause, [<u>check the PostgreSQL documentation</u>](https://www.postgresql.org/docs/current/queries-limit.html). To learn more about other PostgreSQL clauses and basics, visit the [<u>Postgres basics section</u>](https://www.timescale.com/learn/postgres-basics).