AdventureWorks Database: Find the salary of top ten employees

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

120. From the following table write a query in SQL to find the salary of top ten employees. Return BusinessEntityID, Rate, and rank of employees by 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 rank of Rate in descending order
    RANK() OVER (ORDER BY Rate DESC) AS RankBySalary  

-- Selecting data from the EmployeePayHistory table aliased as eph1
FROM 
    HumanResources.EmployeePayHistory AS eph1  

-- Filtering records where RateChangeDate matches the maximum RateChangeDate for each BusinessEntityID
WHERE 
    RateChangeDate = (
        -- Subquery to get the maximum RateChangeDate for each BusinessEntityID
        SELECT MAX(RateChangeDate)   
        FROM HumanResources.EmployeePayHistory AS eph2  
        WHERE eph1.BusinessEntityID = eph2.BusinessEntityID
    )  

-- Ordering the result set by BusinessEntityID
ORDER BY 
    BusinessEntityID

-- 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, Rate, and the rank of Rate in descending order.
• The RANK() function is used to calculate the rank of Rate in descending order. Ranking assigns consecutive rank numbers to rows with the same Rate value, with any gaps in the ranking sequence being skipped.
• Records are filtered to include only those where RateChangeDate matches the maximum RateChangeDate for each BusinessEntityID. This is done using a subquery to find the maximum RateChangeDate for each BusinessEntityID and then comparing it with the RateChangeDate in the outer query.
• The result set is ordered by BusinessEntityID.
• Only the first 10 rows from the result set are fetched using the FETCH FIRST clause.

Sample Output:

businessentityid|rate   |rankbysalary|
----------------+-------+------------+
               1|  125.5|           1|
               2|63.4615|           4|
               3|43.2692|          11|
               4|29.8462|          28|
               5|32.6923|          22|
               6|32.6923|          22|
               7|50.4808|           6|
               8|40.8654|          14|
               9|40.8654|          14|
              10|42.4808|          13|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Rank the products in inventory according to their quantities.
Next: Calculate a row number based on SalesYTD ranking.