How to Find Third or Nᵗʰ Maximum Salary from Salary Table

How to find third or nᵗʰ maximum salary from salary table?

Use ROW_NUMBER(if you want a single) or DENSE_RANK(for all related rows):

WITH CTE AS
(
    SELECT EmpID, EmpName, EmpSalary,
           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
    FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow

How to find third or nᵗʰ maximum salary from salary table?

Use ROW_NUMBER(if you want a single) or DENSE_RANK(for all related rows):

WITH CTE AS
(
    SELECT EmpID, EmpName, EmpSalary,
           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
    FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow

How to fetch the nth highest salary from a table without using TOP and sub-query?

Try a CTE - Common Table Expression:

WITH Salaries AS
(
    SELECT 
       SalaryAmount, ROW_NUMBER() OVER(ORDER BY SalaryAmount DESC) AS 'RowNum'
    FROM 
       dbo.SalaryTable
)
SELECT
  SalaryAmount
FROM
  Salaries
WHERE
   RowNum <= 5

This gets the top 5 salaries in descending order - you can play with the RowNumn value and basically retrieve any slice from the list of salaries.

There are other ranking functions available in SQL Server that can be used, too - e.g. there's NTILE which will split your results into n groups of equal size (as closely as possible), so you could e.g. create 10 groups like this:

WITH Salaries AS
(
    SELECT 
       SalaryAmount, NTILE(10) OVER(ORDER BY SalaryAmount DESC) AS 'NTile'
    FROM 
       dbo.SalaryTable
)
SELECT
  SalaryAmount
FROM
  Salaries
WHERE
   NTile = 1

This will split your salaries into 10 groups of equal size - and the one with NTile=1 is the "TOP 10%" group of salaries.

SQL query to find Nth highest salary from a salary table

You can use a Common Table Expression (CTE) to derive the answer.

Let's say you have the following salaries in the table Salaries:

 EmployeeID  Salary
--------------------
     10101   50,000
     90140   35,000
     90151   72,000
     18010   39,000
     92389   80,000

We will use:

DECLARE @N int
SET @N = 3  -- Change the value here to pick a different salary rank

SELECT Salary
FROM (
    SELECT row_number() OVER (ORDER BY Salary DESC) as SalaryRank, Salary
    FROM Salaries
) as SalaryCTE
WHERE SalaryRank = @N

This will create a row number for each row after it has been sorted by the Salary in descending order, then retrieve the third row (which contains the third-highest record).

• SQL Fiddle

---

For those of you who don't want a CTE (or are stuck in SQL 2000):

[Note: this performs noticably worse than the above example; running them side-by-side with an exceution plans shows a query cost of 36% for the CTE and 64% for the subquery]:

SELECT TOP 1 Salary
FROM 
(
    SELECT TOP N Salary
    FROM Salaries
    ORDER BY Salary DESC
) SalarySubquery
ORDER BY Salary ASC

where N is defined by you.

SalarySubquery is the alias I have given to the subquery, or the query that is in parentheses.

What the subquery does is it selects the top N salaries (we'll say 3 in this case), and orders them by the greatest salary.

If we want to see the third-highest salary, the subquery would return:

 Salary
-----------
80,000
72,000
50,000

The outer query then selects the first salary from the subquery, except we're sorting it ascending this time, which sorts from smallest to largest, so 50,000 would be the first record sorted ascending.

As you can see, 50,000 is indeed the third-highest salary in the example.

3rd highest salary sql

Do a CTE and get the ROWNUMBER() on salary DESC and in outer query fetch the record with rownumber equal to 3.

;WITH CTE AS
(
    SELECT RN = ROW_NUMBER() OVER (ORDER BY salary DESC),
           Salary
     FROM [YourTable]
 )

 SELECT Salary
 FROM CTE
 WHERE RN <= 3

Note: If you want 3rd highest salary use RN=3 if you want all top 3 salary then use RN<=3

If you want top 3 highest salary then you can do this as well:

SELECT TOP 3 Salary
FROM [YourTable]
ORDER BY Salary DESC

SQL query to find out third highest salary involving multiple tables

 select firstname, lastname, salary 
 from 
 (
 select 
    employee.*, details.salary,
    row_number() over (order by salary desc) salaryrank
 from 
      employee
 inner join 
      details
           on employee.empid = details.empid
 ) v
 where salaryrank=3

As for SQL problems with solutions, why not look on stackoverflow itself? Find a question, and see how you can get an answer. Then see what the best rated answer is

How to find the nth largest salary by department using SQLite

If this is homework and you have to use a correlated subquery then you must correlate also the department:

SELECT DISTINCT e1.department, e1.SAL 
FROM EMPLOYEE e1 
WHERE (N - 1) = (
  SELECT COUNT(DISTINCT e2.SAL) 
  FROM EMPLOYEE e2 
  WHERE e2.department = e1.department AND e2.SAL > e1.SAL 
);

A better solution would be to use DENSE_RANK() window function:

SELECT DISTINCT department, SAL
FROM ( 
  SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY SAL DESC) dr
  FROM EMPLOYEE 
)
WHERE dr = N;

Query to find nᵗʰ max value of a column

You could sort the column into descending format and then just obtain the value from the nth row.

EDIT::

Updated as per comment request. WARNING completely untested!

SELECT DOB FROM (SELECT DOB FROM USERS ORDER BY DOB DESC) WHERE ROWID = 6

Something like the above should work for Oracle ... you might have to get the syntax right first!

---

Related Topics