In Redshift database, 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 in conjunction 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.
Syntax:
--Second highest salary SELECT employee_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 employee_name, salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 5;
--TO return top 5 highest earning employees with their salaries: SELECT employee_name, salary FROM employee ORDER BY salary DESC LIMIT 5;
--TO return 6th to 10th highest earning employees SELECT employee_name, salary FROM employee ORDER BY salary DESC LIMIT 5 OFFSET 5;