Execute the following Microsoft SQL Server Transact-SQL script in Query Editor to rank the data set four ways and use one of the ranking to sort the result set.

USE AdventureWorks

GO

-- SQL rank function - SQL OVER clause

SELECT   ContactID,

         FirstName,

         LastName,

         Title = isnull(Title,''),

         RANK()

           OVER(ORDER BY FirstName + LastName)       AS Rank1,

         RANK()

           OVER(ORDER BY LastName + ', ' + FirstName, Title) AS Rank2,

         RANK()

           OVER(ORDER BY LastName + ', ' + FirstName DESC) AS Rank3,

         RANK()

           OVER(ORDER BY LastName DESC, FirstName DESC) AS Rank4

FROM     Person.Contact

WHERE    LEFT(LastName,1) = 'U'

ORDER BY Rank2

GO

/* Partial results

ContactID   FirstName   LastName    Title Rank1 Rank2 Rank3 Rank4

8824        Masaki      Umeda             5     13    13    13

1381        Christopher Underwood         1     17    9     9

21413       Christopher Underwood         1     17    9     9

81751       Christopher Underwood         1     17    9     9

101781      Christopher Underwood         1     17    9     9

20913       Sunil       Uppal             21    21    1     1

*/