Top 5 SQL Server 2005 Analytic Functions

Author: Pradeep Tapadiya, Software Labs Inc.

SQL Server 2005 added many new analytical functions. Here is a gist of most important ones. Enjoy! -Pradeep

ROW_NUMBER

Assigns a sequential identifier to each record of the result set, as in the following example:

SELECT row_number() OVER (ORDER BY OrderDate ASC) AS RowID, OrderId, CustomerID, OrderDate
FROM Orders

Note that this function requires that the input data be ordered OVER one ore more fields.

RANK

This function is similar to Row_Number in the sense that it also works on a ordered set, except all rows within the same ordered value receive the same rank.

Consider the following table:

Monthly Average Temperatue
Year	Month	AvgTemp
1995	June	98
1995	July	100
1996	Aug	102
1996	July	101

How would you find the hottest month in each year? Here is the query:

select rank() OVER (PARTITION BY Year ORDER BY AvgTemp DESC) AS R, * from Temperature

Here is the resulting table

R	Year	Month	AvgTemp
1	1995	July	100
2	1995	June	98
1	1996	Aug	102
2	1996	July	101

Now, pick the records where rank is 1.

DENSE_RANK

This is simlar in functionality to RANK except RANK is not guaranteed to assign ranks in strict sequence where as DENSE_RANK does.

NTILE

This function groups your result set into equal number of records. The following example divides our previously mentioned temperature table into three groups.

select ntile(3) OVER (ORDER BY AvgTemp DESC) AS GroupID, * from Temperature

Each group is assigned a unique identifier.

PIVOT

The PIVOT operator allows you to generate a cross-tab report. The following query will rearrange our Temperature table:

select 
  Year, [June], [July], [Aug]
FROM
  (select * from Temperature) T
PIVOT
(
  Avg(AvgTemp)
  FOR Month IN ([June], [July], [Aug])
) AS pvt

The resulting table is as follows:

Year	June	July	Aug
1995	98	100	(null)
1996	(null)	101	102