You want to generate a ranked list of pay rates for employees who are paid every two weeks by querying the EmployeePayHistory table of the AdventureWorks2012 database. If more than one employee has the same rate, they should all be assigned the same rank. The employee who has the rate that is next below this rate should be ranked from the beginning as shown in the exhibit between rows 7 and 8.

Which of the following Transact-SQL statements can you use to accomplish this task?

 Posted by Rajkatie on 11/30/2012 | Category: Sql Server Interview questions | Views: 3560 | Points: 40
Select from following answers:
  1. SELECT BusinessEntityID, Rate, DENSE_RANK() OVER (ORDER BY Rate DESC) as Rank FROM EmployeePayHistory WHERE PayFrequency = 2;
  2. SELECT BusinessEntityID, Rate, ROW_NUMBER() OVER (ORDER BY Rate DESC) as Rank FROM EmployeePayHistory WHERE PayFrequency = 2;
  3. SELECT BusinessEntityID, Rate, NTILE(5) OVER (ORDER BY Rate DESC) as Rank FROM EmployeePayHistory WHERE PayFrequency = 2;
  4. All Above

Show Correct Answer


Source: Microsoft Press book | | Alert Moderator 

Comments or Responses

Login to post response