Windows functions in SQL Server's T-SQL is not related to the Windows operating system but the kind of more detailed ordering (and all the calculations you can do with the ordered set) than you can get by using the regular
Group By clause. Windows functions are obviously much more powerful than what you get by mere grouping and subqueries.
The following windows functions were introduced in SQL Server 2005:
Row_Number
Rank
Dense_Rank
NTILEThese are enhanced in SQL Server 2012 by providing that provide better analytics,
- Windows Order and Frame clauses
(
Lag, Lead, First_Value, Last_value)
- Windows distribution functions
(
Percent-Rank, Cume_Dist, Percentile_Disc and Percentile_Cont)
Conceptually it is as if you have created a window of rows to which you apply some processing and the result is returned in a single row as related to the windowed rows). You define the window in which the processing takes place by the clause OVER.
The following example is using the Orders and Order Details table in Northwind database. If you need a copy of Northwind follow this link:
The following query when run to get raw data,
Get raw filtered data
[Order Details].OrderID, Cast((Orders.OrderDate) as nvarchar(12)) as 'Order Date', [Order Details].Quantity
FROM [Order Details] INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID and [Order Details].OrderID <=10253
Returns the following:
OrderID Order Date Quantity
----------- ------------ --------
10248 Jul 4 1996 12
10248 Jul 4 1996 10
10248 Jul 4 1996 5
10249 Jul 5 1996 9
10249 Jul 5 1996 40
10250 Jul 8 1996 10
10250 Jul 8 1996 35
10250 Jul 8 1996 15
10251 Jul 8 1996 6
10251 Jul 8 1996 15
10251 Jul 8 1996 20
10252 Jul 9 1996 40
10252 Jul 9 1996 25
10252 Jul 9 1996 40
10253 Jul 10 1996 20
10253 Jul 10 1996 42
10253 Jul 10 1996 40
(17 row(s) affected)
Using Group BY clause:There are groups (17 rows are in 6 groups) and for these groups we can find the sum of quantity orderd like in,
Select [Order Details].OrderID, SUM([Order Details].Quantity) as 'QUAN'
FROM [Order Details] INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID and [Order Details].OrderID <=10253
group by [Order Details].OrderID
the rows returned are,
OrderID QUAN
----------- -----------
10248 27
10249 49
10250 60
10251 41
10252 105
10253 102
(6 row(s) affected)
Using the Rank() function
Now we can rank the value
Quan and show they are ranked using the
Rank() function as in the following:
Select [Order Details].OrderID,
SUM([Order Details].Quantity) as 'QUAN',
RANK() OVER (ORDER BY SUM([Order Details].Quantity)) as RankQuan
FROM [Order Details] INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID and [Order Details].OrderID <=10253
group by [Order Details].OrderID
We have a new column produced by,
RANK() OVER (ORDER BY SUM([Order Details].Quantity)) as RankQuanwhich really shows how the rank is evaluated providing a rank for each row.
The response to the above is,
OrderID QUAN RankQuan----------- ----------- --------------------
10248 27 1
10251 41 2
10249 49 3
10250 60 4
10253 102 5
10252 105 6
(6 row(s) affected)
As you notice the Rank() function took care of ordering as well in addition to ranking them.
Use Rank() function one more time:
Let us modify the query further
(and answer the questions whether one can use another Rank() function.
Select [Order Details].OrderID,
SUM([Order Details].Quantity) as 'QUAN',
RANK() OVER (ORDER BY SUM([Order Details].Quantity)) as RankQuan,
Rank() OVER (ORDER BY [Order Details].OrderID) as 'OrdIDRank'
FROM [Order Details] INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID and [Order Details].OrderID <=10253
group by [Order Details].OrderID
The response is as follows:
OrderID QUAN RankQuan OrdIDRank
----------- ----------- -------------------- --------------------
10248 27 1 1
10249 49 3 2
10250 60 4 3
10251 41 2 4
10252 105 6 5
10253 102 5 6
(6 row(s) affected)
You can see that this has not affected the RankQuan except that the ordering in the final rank gets ordered consequitively.
