SQL Query to find the Nth Largest Value in a Column using Limit and Offset
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
Share your thoughts in the comments
Please Login to comment...