AdventureWorks Database: Return the top ten employees ranked by their salary

Last update on December 20 2024 11:37:21 (UTC/GMT +8 hours)

117. From the following table write a query in SQL to return the top ten employees ranked by their salary.

Sample table: HumanResources.EmployeePayHistory

businessentityid|ratechangedate         |rate   |payfrequency|modifieddate           |
----------------+-----------------------+-------+------------+-----------------------+
               1|2009-01-14 00:00:00.000|  125.5|           2|2014-06-30 00:00:00.000|
               2|2008-01-31 00:00:00.000|63.4615|           2|2014-06-30 00:00:00.000|
               3|2007-11-11 00:00:00.000|43.2692|           2|2014-06-30 00:00:00.000|
               4|2007-12-05 00:00:00.000|   8.62|           2|2007-11-21 00:00:00.000|
               4|2010-05-31 00:00:00.000|  23.72|           2|2010-05-16 00:00:00.000|
               4|2011-12-15 00:00:00.000|29.8462|           2|2011-12-01 00:00:00.000|
               5|2008-01-06 00:00:00.000|32.6923|           2|2014-06-30 00:00:00.000|
               6|2008-01-24 00:00:00.000|32.6923|           2|2014-06-30 00:00:00.000|
               7|2009-02-08 00:00:00.000|50.4808|           2|2014-06-30 00:00:00.000|
               8|2008-12-29 00:00:00.000|40.8654|           2|2014-06-30 00:00:00.000|
			   -- more --

Click to view Full table

Sample Solution:

-- Selecting specific columns from the EmployeePayHistory table
SELECT 
    -- Selecting the BusinessEntityID column
    BusinessEntityID, 

    -- Selecting the Rate column
    Rate,   

    -- Calculating the dense rank of Rate in descending order
    DENSE_RANK() OVER (
        -- Ordering the data by Rate in descending order
        ORDER BY Rate DESC
    ) AS RankBySalary  

-- Selecting data from the EmployeePayHistory table
FROM 
    HumanResources.EmployeePayHistory

-- Fetching only the first 10 rows from the result set
FETCH FIRST 10 ROWS ONLY;

Explanation:

This SQL code selects specific columns from the EmployeePayHistory table.
It retrieves data related to employee pay history, including BusinessEntityID and Rate.
The DENSE_RANK() function is used to calculate the dense rank of Rate in descending order. Dense ranking assigns consecutive rank numbers to rows with the same Rate value, without any gaps.
The data is ordered by Rate in descending order before calculating the dense rank.
Only the first 10 rows from the result set are fetched using the FETCH FIRST clause.
The result set will contain the BusinessEntityID, Rate, and the dense rank of Rate for the top 10 highest salaries.

Sample Output:

businessentityid|rate   |rankbysalary|
----------------+-------+------------+
               1|  125.5|           1|
              25|84.1346|           2|
             273|72.1154|           3|
               2|63.4615|           4|
             234|60.0962|           5|
             263|50.4808|           6|
               7|50.4808|           6|
             234|48.5577|           7|
             287| 48.101|           8|
             274| 48.101|           8|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Rank the products by the specified inventory locations.
Next: Divide rows into defined groups based on SalesYTD.