PostgreSQL – OFFSET Clause

In Postgres, an OFFSET clause is used to skip the first ‘n’ number of rows specified by offset clause before returning the result set.

Often, OFFSET is used together with LIMIT clause and ORDER BY clause. LIMIT allows you to restrict the number of rows returned. OFFSET allows you to skip the first few rows; ORDER BY allows you to order the result set before applying the limit and offset functionalities.

Example: You can get the nth highest salary by skipping the first (n-1) rows ordered in descending order.

Consider the employee table as shown below:

id	first_name	last_name	department	salary
101	bryan	walker	sales	50,000
102	alex	carpenter	accounts	55,000
103	sam	white	sales	60,000
104	john	barry	sales	54,000
105	bill	wood	accounts	65,000
106	robert	hopkins	support	70,000
107	david	lee	support	72,000
108	jack	sears	support	65,000
109	tom	young	sales	55,000

Syntax:

--Second highest salary
SELECT first_name, last_name, salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1;

Above query returns the employee with 2nd highest salary and their salary. OFFSET 1 skips the first highest salary; LIMIT 1 makes sure to return only the 2nd highest salary row;

--TO return 6th highest salaried employee
SELECT first_name, last_name, salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 5;

--TO return top 5 highest earning employees with their salaries:
SELECT first_name, last_name, salary FROM employee ORDER BY salary DESC LIMIT 5;

--TO return 6th to 10th highest earning employees
SELECT first_name, last_name, salary FROM employee ORDER BY salary DESC LIMIT 5 OFFSET 5;