Skip to content
Related Articles

Related Articles

Improve Article
SQL Query to find the Nth Largest Value in a Column using Limit and Offset
  • Difficulty Level : Medium
  • Last Updated : 06 Nov, 2020

Prerequisite – How to find Nth highest salary from a table
Problem Statement : Write an SQL query to find the nth largest value from the column using LIMIT and OFFSET.

Example-1 :

Table – BILLS

FLATNo.ElectricityBill
1011000
1021500
1031300
2012300
2026700
2037500
2041300
3012300



The above table has the electricity bills of all the flats in an apartment. You have to find the nth largest electricity bill in the table.

SELECT DISTINCT ElectricityBill AS NthHighestElectricityBill
FROM Bills
ORDER BY ElectricityBill DESC
LIMIT 1 
OFFSET n-1;

Here n should be an integer whose value must be greater than zero.

Explanation :
In the above query, we are sorting the values of ElectricityBill column in descending order using Order By clause and by selecting only distinct values. After sorting it in descending order we have to find the Nth value from the top, so we use OFFSET n-1 which eliminates the top n-1 values from the list, now from the remaining list we have to select only its top element, to do that we use LIMIT 1.

If we want to find the 3rd highest electricity bill the query will be –

SELECT DISTINCT ElectricityBill AS 3rdHighestElectricityBill
FROM Bills
ORDER BY ElectricityBill DESC
LIMIT 1
OFFSET 2;

The result of the above query will be –

3rdHighestElectricityBill
2300




Example-2 :

Table – EmployeeSalary

EmployeeIDSalaryInThousands
1A234450
1D765259
5A567320
3B653450
3A980259
9R345128
2A748316

The above table has the salaries of employees working in a small company. Find the employee id who is earning the 4th highest salary.

SELECT EmployeeID AS 4thHighestEarningEmployee 
FROM EmployeeSalary
ORDER BY SalaryInThousands DESC
LIMIT 1 
OFFSET 3;

Explination :
Here distinct is not used because we need employee whose earnings stand at 4th place among all the employee’s (i.e 316k not 259k).

The result of the above query will be –

4thHighestEarningEmployee
2A748

My Personal Notes arrow_drop_up
Recommended Articles
Page :