There is a new feature in SQL Server 2005 that is akin to the ROWNUM function in Oracle which I am going to blog about here. I'm sure alot of people reading will already know about this function and its syntax/behaviour but this post may still serve as a useful reference. I'm mainly writing this for my colleagues at Conchango that won't have come across it yet.
I am going to demo using the sample database AdventureWorks that comes with SQL Server 2005 so the first thing you'll want to do is install the sample DB and issue
There is a view in this database called Sales.vSalesPerson (note the Schema.ObjectName qualification rather than Owner.ObjectName that existing SQL Server users will be used to). This view provides, for each sales person, SalesYTD, SalesLastYear and the sales person's country region.
returns the following data:
But lets suppose we want to rank the salespeople based on their SalesYTD. This is where the ROW_NUMBER function comes into play. The syntax (as given in BOL) is:
|ROW_NUMBER ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
< OVER_CLAUSE > :: =
OVER ( [ PARTITION BY < value_expression > , ... [ n ] ]
So let's rewrite our SQL statement:
|select row_number() over (order by SalesYTD asc) as rownum, CountryRegionName, FirstName, LastName, SalesYTD, SalesLastYear from Sales.vSalesPerson
and see what that gives us:
As you can see 2 things have happened:
1) The results have been ordered in ascending order according to SalesYTD
2) There is a new column ("rownum") indicating the order within the dataset.
But wait. There's more!!
As you can see above the syntax allows for the provision of a PARTITION BY clause. This enables you to first group the rows according to a given attribute and then rank them within that group.
Let's add this to our SQL statement:
|select row_number() over (partition by CountryRegionName order by SalesYTD asc) as rownum, CountryRegionName, FirstName, LastName, SalesYTD, SalesLastYear|
This time we get:
You can see that the records have been grouped according to CountryRegionName prior to applying the value from ROW_NUMBER(). Straightaway we can see which are our best sales people in each region.
Of course, you can include multiple expressions in the PARTITION BY clause to enable you to apply groupings and sub-groupings to your data.
This is just a taste of one of the new features. Hopefully this post will be of use to someone!
Note: This information is based on SQL Server 2005 Beta 2!