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

SQL Query - AdventureWorks: Exercise-117 with Solution

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

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.