SQL Query

How to display nth lowest record in a table for example? How to display 4th lowest (salary) record from customer table?

Questions by rohitdeepu17

Editorial / Best Answer

Answered by: Yadnesh

  • Jun 1st, 2013


-- First of all Customers table wont have salary attribute. we need to use employees/employeesSalary table -- where we can find salary attribute. -- for finding nth lowest salary record we can obtain derived column using DENSE_RANK() in a subquery and -- obtain temporary result set and finding nth record in a temporary result set using WHERE clause -- for the given example, following is the solution.

Code
  1. SELECT x.rate
  2. ,x.rankedSalary
  3. FROM (SELECT rate
  4.                 ,DENSE_RANK() OVER (ORDER BY rate) AS rankedSalary
  5.                 FROM HumanResources.EmployeePayHistory
  6.                 GROUP BY rate) x
  7. WHERE x.rankedSalary = 4

Showing Answers 1 - 1 of 1 Answers

Yadnesh

  • Jun 1st, 2013
 

-- First of all Customers table wont have salary attribute. we need to use employees/employeesSalary table
-- where we can find salary attribute.
-- for finding nth lowest salary record we can obtain derived column using DENSE_RANK() in a subquery and
-- obtain temporary result set and finding nth record in a temporary result set using WHERE clause

-- for the given example, following is the solution.

Code
  1. SELECT x.rate

  2. ,x.rankedSalary

  3. FROM (SELECT rate

  4.                 ,DENSE_RANK() OVER (ORDER BY rate) AS rankedSalary

  5.                 FROM HumanResources.EmployeePayHistory

  6.                 GROUP BY rate) x

  7. WHERE x.rankedSalary = 4

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions