Using Ranking and Windowing Functions in SQL Server 2005

1
Using Ranking and Windowing Functions in SQL
Server 2005

• Stephen Forte
• CTO, Corzen Inc
• Microsoft Regional Director NY/NJ (USA)

2
Speaker.Bio.ToString()

• CTO and co-Founder of Corzen, Inc
• Microsoft Regional Director for New York NJ
• Blog http//www.stephenforte.net/owdasblog/
• Wrote a few books (Jet, VB, SQL Server, Access)
• Writing SQL Server 2005 Core Developers Guide for
  MS Press due out in 05
• International Conference Speaker for 8 Years
• Co-moderator founder of NYC .NET Developers
  Group
• http//www.nycdotnetdev.com
• Former CTO of Zagat Survey
• Hobbies include high altitude trekking, scuba
  diving, hiking, rock climbing and triathlon

3
Session Disclaimer Notes

• All code against SQL Server 2005 June 2005 CTP
  beta
• You can get it for free from
• www.microsoft.com/sqlserver
• Content is subject to change in final release but
  if so will update this session on my blog

4
Agenda

• Ranking Functions
• Windowing Functions
• Ranking OVER other Aggregates

5
SQL Server 2005 Platform
6
Ranking Functions

• Adds a column to resultset based on ranking
• Functions are
• ROW_NUMBER
• RANK
• DENSE_RANK
• NTILE(n)

7
Ordering

• Column to be rated specified in ORDER BY clause
• there must be at least one ordering column
• can be more than one
• determines the Order By of your output

SELECT orderid, customerid, ROW_NUMBER()
OVER(ORDER BY orderid) AS num FROM orders WHERE
orderid lt 10400 AND customerid lt 'BN'
8
ROW_NUMBER()

• Returns a column as an expression that contains
  the rows number in the result set
• Only used in the context of the resultset
• if the result changes, the ROW_NUMBER() will
  change
• If you want to use the Row_Number in a where
  clause or aggergate you must use a Common Table
  Expression

9
ROW_NUMBER()
Select SalesOrderID, CustomerID, Row_Number()
Over (Order By SalesOrderID) as RunningCount From
Sales.SalesOrderHeader Where SalesOrderIDgt10000 Or
der By SalesOrderID
lastname country age
rank -------------------- ---------------
----------- ------ Dodsworth UK
37 1 Suyama UK
40 2 King UK
43 3 Buchanan UK
48 4 Leverling USA
40 1 Callahan USA
45 2 Fuller USA
51 3 Davolio
USA 55 4 Peacock
USA 66 5
10
ROW_NUMBER()
11
RANK()

• RANK() works a lot like ROW_NUMBER() except that
  it will not break ties
• you will not get a unique value for ties

12
RANK()
Select SalesOrderID, CustomerID, RANK() Over
(Order By CustomerID) as RunningCount From
Sales.SalesOrderHeader Where SalesOrderIDgt10000 Or
der By CustomerID
SalesOrderID CustomerID RunningCount ------------
----------- -------------------- 43860 1
1 44501 1 1 45283
1 1 46042 1 1 46976
2 5 47997 2
5 49054 2 5 50216 2
5 51728 2 5 57044 2
5 63198 2 5 69488
2 5 44124 3 13
13
RANK()
14
DENSE_RANK()

• DENSE_RANK works exactly like RANK() but will
  remove the skipping of numbers in the tie.

15
DENSE_RANK()
Select SalesOrderID, CustomerID, DENSE_RANK()
Over (Order By CustomerID) as RunningCount From
Sales.SalesOrderHeader Where SalesOrderIDgt10000 Or
der By CustomerID
SalesOrderID CustomerID RunningCount ------------
----------- -------------------- 43860 1
1 44501 1 1 45283
1 1 46042 1 1 46976
2 2 47997 2
2 49054 2 2 50216 2
2 51728 2 2 57044 2
2 63198 2 2 69488
2 2 44124 3 3
16
DENSE_RANK()
17
NTILE(n)

• NTile(n) will evenly divide all the results into
  approximately even pieces and assign each piece
  the same number in the resultset.
• A example is the percent of 100 (like for an
  examination in University) or a percentile of
  runners in a road race.

18
NTILE(n)
Select SalesOrderID, CustomerID, NTILE(10000)
Over (Order By CustomerID) as RunningCount From
Sales.SalesOrderHeader Where SalesOrderIDgt10000 Or
der By CustomerID
SalesOrderID CustomerID RunningCount ------------
----------- -------------------- 43860 1
1 44501 1 1 45283
1 1 46042 1 1 46976
2 2 47997 2
2 49054 2 2 50216 2
2 51728 2 3 57044 2
3 63198 2 3 69488
2 3 44124 3 4
19
NTILE(n)
20
Agenda

• Ranking Functions
• Windowing Functions
• Ranking OVER other Aggregates

21
Windowing

• You can divide the resultset into subgroups
• known as windows
• use "PARITITION BY" in the OVER clause
• each partition has its own ranking

22
Windowing
Select SalesOrderID, SalesPersonID,
OrderDate, Row_NUMBER() Over (Partition By
SalesPersonID Order By OrderDate) as
OrderRank From Sales.SalesOrderHeader Where
SalesPersonID is not null
SalesOrderID SalesPersonID OrderDate
OrderRank ------------ -------------
----------------------- --- 43659 279
2001-07-01 000000.000 1 43660 279
2001-07-01 000000.000 2 43681
279 2001-07-01 000000.000 3 43684
279 2001-07-01 000000.000 4 43685
279 2001-07-01 000000.000
5 43694 279 2001-07-01
000000.000 6 43695 279
2001-07-01 000000.000 7 43696 279
2001-07-01 000000.000 8 43845 279
2001-08-01 000000.000 9 43861 279
2001-08-01 000000.000 10 . . .
More 48079 287 2002-11-01
000000.000 1 48064 287
2002-11-01 000000.000 2 48057 287
2002-11-01 000000.000 3 47998 287
2002-11-01 000000.000 4 48001 287
2002-11-01 000000.000 5 48014
287 2002-11-01 000000.000 6 47982
287 2002-11-01 000000.000 7 47992
287 2002-11-01 000000.000
8 48390 287 2002-12-01
000000.000 9 48308 287
2002-12-01 000000.000 10
23
PARTITION BY
24
Agenda

• Ranking Functions
• Windowing Functions
• Ranking OVER other Aggregates

25
OVER and other aggregates

• OVER can be used with other aggregates
• includes user-defined aggregates
• usually produces groups of duplicate values

26
Over with max aggregate
-- there is one oldest employee age for each
country select , RANK() OVER(PARTITION BY
COUNTRY ORDER BY age) as rank, MAX(age)
OVER(PARTITION BY COUNTRY) as oldest age in
country from ( select lastname, country,
datediff(yy,birthdate,getdate()) as age from
employees ) as a
lastname country age
rank oldest age
in country ---------------
----- --------------- ----------- ------
---------- Dodsworth UK
37 1 48 Suyama UK
40 2 48 King
UK 43 3 48 Buchanan
UK 48 4
48 Leverling USA 40
1 66 Callahan USA
45 2 66 Fuller USA
51 3 66 Davolio
USA 55 4 66 Peacock
USA 66 5 66
27
Session Summary

• New TSQL Operators that will allow Ranking
• Can also apply to aggregates

28
Questions?
29
Thanks!

• Please fill out your evaluation form!
• stevef_at_orcsweb.com
• Please put (PDC Karachi in the subject)