PostgreSQL LIMIT Clause

PostgreSQL LIMIT Clause

The PostgreSQL LIMIT clause is used to limit the number of rows returned by a query. This clause is essential for controlling the size of the result set and improving query performance.

---

Syntax

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

The LIMIT clause has the following component:

• number_of_rows: The maximum number of rows to return.

---

Example PostgreSQL LIMIT Clause Queries

Let's look at some examples of PostgreSQL LIMIT clause queries:

1. Basic LIMIT Example

SELECT first_name, last_name
FROM employees
LIMIT 5;

This query retrieves the first 5 rows from the employees table.

2. LIMIT with OFFSET

SELECT first_name, last_name
FROM employees
LIMIT 5 OFFSET 10;

This query retrieves 5 rows from the employees table, starting from the 11th row.

3. LIMIT with ORDER BY

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

This query retrieves the top 5 highest salaries from the employees table, sorted in descending order by salary.

4. LIMIT with DISTINCT

SELECT DISTINCT department_id
FROM employees
LIMIT 3;

This query retrieves the first 3 unique department_id values from the employees table.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the LIMIT clause to retrieve a specific number of rows.

Step 1: Creating a Table

This step involves creating a new table named employees to store employee data.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    salary NUMERIC(10, 2),
    department_id INT
);

In this example, we create a table named employees with columns for id, first_name, last_name, email, salary, and department_id.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('John', 'Doe', 'john.doe@example.com', 50000, 1);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Jane', 'Smith', 'jane.smith@example.com', 60000, 2);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Jim', 'Brown', 'jim.brown@example.com', 55000, 1);

INSERT INTO employees (first_name, last_name, email, salary, department_id)
VALUES ('Emily', 'Jones', 'emily.jones@example.com', 70000, 2);

Here, we insert data into the employees table.

Step 3: Using the LIMIT Clause

This step involves using the LIMIT clause to retrieve a specific number of rows from the employees table.

These queries demonstrate how to use the LIMIT clause to retrieve a specific number of rows from the employees table, including using LIMIT with OFFSET, ORDER BY, and DISTINCT.

-- Basic example with LIMIT=2

SELECT first_name, last_name
FROM employees
LIMIT 2;

-- Basic example with LIMIT=3

SELECT first_name, last_name
FROM employees
LIMIT 3;

-- LIMIT with OFFSET

SELECT first_name, last_name
FROM employees
LIMIT 2 OFFSET 1;

-- LIMIT with ORDER BY

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 2;

-- LIMIT with DISTINCT

SELECT DISTINCT department_id
FROM employees
LIMIT 2;

Conclusion

The PostgreSQL LIMIT clause is a fundamental tool for controlling the size of the result set and improving query performance. Understanding how to use the LIMIT clause and its syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.