SQL query to find Nth highest salary
First, the query will return the nth
lowest salary value. To return the nth
highest salary value you must change t.sal <= sal
to t.sal >= sal
.
Next, this query works by first finding the distinct list of salary values as one derived table and then determines the number of employees that have a salary less than each one in this list. t.sal <= sal
is taking the derived table (which most databases would require have an alias) and comparing each value against the outer emp
table. It should be noted that this will return multiple rows in the case of a tie.
To manually trace the output, we need some inputs:
Alice | 200
Bob | 100
Charlie | 200
Danielle | 150
Select Distinct sal
From emp
Gives us
200
100
150
Now we analyze each row in the outer table
Alice - There are 3 distinct salary values less than or equal to 200
Bob - 1 rows <= 100
Charlie - 3 rows <= 200
Danielle - 2 row <= 150
Thus, for each salary value we get the following counts (and reordered by count):
Bob 1
Danielle 2
Charlie 3
Alice 3
The most important aspect that I think you are overlooking is that the outer emp
table is correlated to the inner count calculation (which is why it is called a correlated subquery). I.e., for each row in the outer emp
table, a new count is calculated for that row's salary via t.sal <= sal
. Again, most database systems would require the inner most query to have an alias like so (note the As Z
alias):
Select sal
From emp As t
Where &n = (
Select Count(Z.sal)
From (
Select Distinct sal
From emp
) As Z
Where t.sal <= Z.sal
)
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
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.
find nth highest salary in sql
SELECT * FROM EMPLOYEE e1
WHERE N-1 =
(SELECT COUNT(e2.ORIG_SALARY) FROM EMPLOYEE e2
WHERE e2.ORIG_SALARY > e1.ORIG_SALARY)
COUNT(e2.ORIG_SALARY)
outputs the amount of entries in this column in the returned data set. The data set is all the salaries higher then the one in the row your main query returns.
WHERE N-1 =
means that it will get the result where that count matches N-1
So that means you will get the a row of the table employee where there are N-1
higher salaries in the table, effectively giving you the row with the nth highest salary.
Keep in mind however, this is not perfect. For instance if you have multiple people with the same salary in the top 6, you don't get the nth highest salary, you get the nth highest sorted descending by salary.
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.
Find out the nth-highest salary from table
you can use something like this.. this is what i have tested and then pasted here
SELECT *
FROM tblname
WHERE salary = (SELECT *
FROM (SELECT *
FROM (SELECT *
FROM (SELECT DISTINCT( salary )
FROM tblname
ORDER BY salary DESC) A
WHERE rownum <= nth) B
ORDER BY salary ASC) C
WHERE rownum <= 1)
in place of 'tblname' give your table name and then in place nth give your desired nth highest salary that you want
you can see in the screen shot that it is working.
Related Topics
SQL Updating from an Inner Join
SQL Sum Data from Multiple Tables
How to Import Text Files with the Same Name and Schema But Different Directories into Database
Way to Try Multiple Selects Till a Result Is Available
Is There Common Street Addresses Database Design for All Addresses of the World
How to Check If a Stored Procedure Exists Before Creating It
Efficient SQL Test Query or Validation Query That Will Work Across All (Or Most) Databases
Is There Ever a Time Where Using a Database 1:1 Relationship Makes Sense
Replacing Null with 0 in a SQL Server Query
Insert Picture into SQL Server 2005 Image Field Using Only SQL
Seeing the Underlying SQL in the Spring Jdbctemplate
SQL Group by Case Statement with Aggregate Function
Does Db2 Have an "Insert or Update" Statement