Open In App

SQL Query to find the Nth Largest Value in a Column using Limit and Offset

Improve
Improve
Like Article
Like
Save
Share
Report

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
101 1000
102 1500
103 1300
201 2300
202 6700
203 7500
204 1300
301 2300

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 

 

EmployeeID SalaryInThousands
1A234 450
1D765 259
5A567 320
3B653 450
3A980 259
9R345 128
2A748 316

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;

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

 


Last Updated : 11 Nov, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads