SQLUSA

Microsoft SQL Server 2005 Best Practices

How to construct multiple rankings of a data set?

 

Execute the following 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
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

 

SQLUSA - The Best SQL Training in the World
 
 
SQLUSA.com Home Page