have you any wool?

About 5 months into my current job, I was asked to come up with a report that ranked scores in order of best to worst. My intial intention was to just order the data by the score and print out a count in the Rank Column. (Gimme a break, I was fairly new). This idea was shot when I was told the report had to be sortable by any column.

After spending quite some time on Google, I ended up finding a way to rank in SQL 2000 by joining two identical statements together and doing some trickery on the Join Clause to get what I wanted. Not at all and ideal solution.

In SQL 2005, we have the Rank statement. This statement is very easy to use and comes in handy in many, many reporting scenarions. An example using the AdventureWorksLT database:

Select
	P.[Name] as ProductName,
	cast(sum(LineTotal) as Decimal(10,2)) as TotalSales,
	Rank() Over(Order By sum(LineTotal) Desc) as SalesRank
From SalesLT.SalesOrderHeader SOH
Inner Join SalesLT.SalesOrderDetail SOD on SOD.SalesOrderID = SOH.SalesOrderID
Inner Join SalesLT.Product P on P.ProductID = SOD.ProductID
Group By P.[Name]
Order By P.[Name]

The way this works is that the rank statement takes the Over(Order by XX Clause) and assigns a rank to each row based on where it would fall if you were to order the statement by XX. The great thing is, you don’t have to order the statement by column XX. You can order it by any column you wish and the rank still works.