Related Articles

Related Articles

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

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

The result of the above query will be –


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

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 –


My Personal Notes arrow_drop_up
Recommended Articles
Page :