Monday, July 7, 2008

sql query to find nth largest

Its a famouse question ,if you have attended any interviews as a fresher.
There is no direct or single query to find out as far as I know.We have to use 2 queries and its as follows

Query to find 2nd largest
SELECT TOP (1) salary_amt, emp_id
FROM (SELECT TOP (2) salary_amt, emp_id
FROM salary ORDER BY salary_amt DESC) AS a
ORDER BY salary_amt
Little amount of explanation.
Consider the sample data below .The Name of the Table is Salary and it has 2 columns named emp_id and Salary_amt

Salary(emp_id,salary_amt)
  • (1,1000)
  • (2,500)
  • (3,4000)
  • (4,1000)
  • (5,5000)
  • (6,3000)
Lets start from the inner query which is in italics.This orders the records in descending order and returns top 2 records.
The result will be
  • (5,5000)
  • (3,4000)

Now we are querying on the result which we got.It sort the data(The above result) in ascending order and takes the top most and that is the 2nd largest.

  • (3,4000)

This can be generalized as follows for any value of n

SELECT TOP (1) salary_amt, emp_id
FROM (SELECT TOP (n) salary_amt, emp_id
FROM salary ORDER BY salary_amt DESC
) AS a
ORDER BY salary_amt

This can be altered to suit your situations such as nth smallest

SELECT TOP (1) salary_amt, emp_id
FROM (SELECT TOP (n) salary_amt, emp_id
FROM salary ORDER BY salary_amt) AS a
ORDER BY salary_amt DESC

No comments: