How to find all of the fifth highest salaried employees in a single query in SQL Server
In SQL Server 2005 and up, you can use one of the ranking functions to achieve this:
;WITH RankingEmployees AS
(
SELECT
EmpID,
DENSE_RANK() OVER(ORDER BY Salary DESC) 'SalaryRank'
FROM dbo.Employees
)
SELECT
*
FROM
RankingEmployees
WHERE
SalaryRank = 5
Using DENSE_RANK
will give all employees of the same salary the same rank, e.g. you'll get the fifth highest salary and all employees that have that salary.
How to select the nth row in a SQL database table?
There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.
A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.
Basically, PostgreSQL and MySQL supports the non-standard:
SELECT...
LIMIT y OFFSET x
Oracle, DB2 and MSSQL supports the standard windowing functions:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n
(which I just copied from the site linked above since I never use those DBs)
Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.
Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.
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.
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
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
)
SQL Query to find Nth highest salary
;WITH cte1
AS
(
SELECT ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS RN, * FROM Salaries
)
SELECT *
FROM cte1
WHERE RN = 5 <-- Nth highest
Related Topics
Calculate Fiscal Year in SQL Select Statement
Bigquery SQL: Average, Geometric Mean, Remove Outliers, Median
SQL Query to Create a Calculated Field
Query a Table and a Column Name Stored in a Table
Tsql Aggregate String for Group By
Is There a Function That Takes a Year, Month and Day to Create a Date in Postgresql
Mysql: Name Primary Key in Create Table Statement
Sql: Filter Rows with Max Value
How to Pass Column Name as Parameter in Select Statement SQL Server
Compare Deleted and Inserted Table in SQL Server 2008
Can SQL Clr Triggers Do This? or Is There a Better Way
Can Scalar Functions Be Applied Before Filtering When Executing a SQL Statement
Sum of Digits of a Number in SQL Server Without Using Traditional Loops Like While
Performance of Querying for a String That Starts and Ends with Something
How to Generate All N-Grams in Hive
Find Out the Calling Stored Procedure in SQL Server