Select Top 10 Rows from Table in PostgreSQL

In PostgreSQL, selecting the top rows from a table is a common requirement when you want to retrieve only a subset of data, such as the most recent entries, the highest values, or the first few rows for analysis. PostgreSQL provides several ways to achieve this, such as using the LIMIT clause or combining it with the ORDER BY clause to control the selection order.

In this tutorial, we will explore how to select the top 10 rows from a table, with practical examples and detailed explanations for each query to understand its application and usage.

Basic Syntax

The LIMIT clause is used in PostgreSQL to specify the maximum number of rows to return in a query result. When combined with the ORDER BY clause, you can control which rows are returned based on sorting criteria.

</>
Copy
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
LIMIT 10;

Explanation: The query retrieves a maximum of 10 rows from table_name, sorted by column_name. If no ORDER BY clause is specified, rows are returned in an arbitrary order.

Example 1: Selecting Top 10 Rows Without Sorting

Let’s start by creating a table named employees and inserting some sample data:

</>
Copy
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

INSERT INTO employees (employee_name, department, salary)
VALUES 
('Alice', 'HR', 50000),
('Bob', 'IT', 60000),
('Charlie', 'Finance', 70000),
('David', 'Marketing', 55000),
('Eve', 'IT', 72000),
('Frank', 'Finance', 50000),
('Grace', 'HR', 45000),
('Hank', 'IT', 80000),
('Ivy', 'Marketing', 67000),
('Jack', 'Finance', 40000),
('Kara', 'HR', 52000),
('Leo', 'Marketing', 60000);
PostgreSQL - Select Top 10 Rows from Table - Example table

To retrieve the first 10 rows from the employees table, use the LIMIT clause:

</>
Copy
SELECT * FROM employees
LIMIT 10;

Result:

PostgreSQL - Select Top 10 Rows from Table

Explanation: This query returns the first 10 rows from the employees table. Since there is no ORDER BY clause, the rows are returned in an arbitrary order, which is generally the order in which they were inserted.

Example 2: Selecting Top 10 Rows with Sorting

To retrieve the 10 employees with the highest salaries, use the ORDER BY clause with LIMIT:

</>
Copy
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10;
PostgreSQL - Selecting Top 10 Rows with Sorting

Explanation: The ORDER BY salary DESC sorts the rows in descending order of salary. The LIMIT 10 clause ensures that only the top 10 rows based on salary are returned. This query is useful when identifying the highest earners in an organization.

Example 3: Selecting Top 10 Rows Based on Multiple Criteria

You can sort the rows based on multiple columns to refine the selection. For example, to retrieve the top 10 employees based on salary and, in case of ties, sort by name alphabetically:

</>
Copy
SELECT * FROM employees
ORDER BY salary DESC, employee_name ASC
LIMIT 10;
PostgreSQL - Selecting Top 10 Rows Based on Multiple Criteria

Explanation: The query first sorts rows by salary in descending order. If two or more employees have the same salary, they are further sorted by employee_name in ascending order. This approach ensures a well-defined order when there are ties in the primary sorting column.

Conclusion

PostgreSQL provides powerful tools like the LIMIT clause and window functions to retrieve the top rows from a table. Whether you need to fetch the first few rows, identify top performers, or analyze data within groups, these techniques help you query data efficiently. By combining LIMIT with sorting and filtering criteria, you can tailor your queries to meet a wide range of requirements.