w3resource

AdventureWorks Database: Find the salary of top ten employees

SQL Query - AdventureWorks: Exercise-120 with Solution

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.

Sample table: HumanResources.EmployeePayHistory


Click to view Full table

Sample Solution:

SELECT BusinessEntityID, Rate,   
       RANK() OVER (ORDER BY Rate DESC) AS RankBySalary  
FROM HumanResources.EmployeePayHistory AS eph1  
WHERE RateChangeDate = (SELECT MAX(RateChangeDate)   
                        FROM HumanResources.EmployeePayHistory AS eph2  
                        WHERE eph1.BusinessEntityID = eph2.BusinessEntityID)  
ORDER BY BusinessEntityID
FETCH FIRST 10 ROWS ONLY;

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.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Convert Timestamp to date in MySQL Query:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted'

Database: MySQL

Ref : https://bit.ly/3EJPnMQ