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 : 30 Jun, 2021
Geek Week

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. 

Explaination : 
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;

Explaination : 
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

 

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :