{"id":1598,"date":"2013-03-05T00:00:00","date_gmt":"2013-03-05T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-2012-window-function-basics\/"},"modified":"2021-09-29T16:21:45","modified_gmt":"2021-09-29T16:21:45","slug":"sql-server-2012-window-function-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-2012-window-function-basics\/","title":{"rendered":"SQL Server 2012 Window Function Basics"},"content":{"rendered":"
\n

When Microsoft released SQL Server 2005, they included support for window functions, built-in T-SQL functions that can be applied to a result set’s partitioned rows-windows<\/i>-in order to rank or aggregate data in each partition. However, support for window functions was fairly limited in SQL Server 2005, and those limitations carried into 2008. But SQL Server 2012 has pushed through those constraints by expanding the capabilities of existing window functions and by adding new functions that support windowing.<\/p>\n

SQL Server 2012 now includes three types of window functions: ranking, aggregate, and analytic. Ranking functions return a ranking value for each row in a partition. Aggregate functions perform a calculation on a column’s values within a partition, such as finding the total or average of those values. Ranking functions first appeared in SQL Server 2005 with the advent of window function support. Aggregate functions have been around forever.<\/p>\n

Analytic functions are new to SQL Server 2012. An analytic function computes an aggregate value based on the values in a column within a partition. However, analytic functions go beyond simple aggregate ones by being able to take such actions as returning the first or last value in an ordered partitioned set, retrieving the previous or next value in that set, or calculating percentages based on the set’s cumulative values.<\/p>\n

\n

NOTE: For more details about analytic functions, see the topic “Analytic Functions (Transact-SQL)<\/a>” in SQL Server Books Online. Also note that SQL Server 2012 supports another window function, NEXT<\/code><\/b> VALUE<\/code><\/b> FOR<\/code><\/b>, which is not considered a rank, aggregate, or analytic function. The function generates a sequence number based on a specified sequence object. A discussion of the NEXT<\/code><\/b> VALUE<\/code><\/b> FOR<\/code><\/b> function is beyond the scope of this article, but you can find details about the function in the topic “NEXT VALUE FOR (Transact-SQL)<\/a>” in SQL Server Books Online.<\/p>\n<\/div>\n

The key to understanding SQL Server window functions is in the OVER<\/code><\/b> clause, which can be defined on any window function used in a query’s select list. The clause determines how to partition and sort a result set in order to apply the window function. In fact, you can think of a window function as one that supports the OVER<\/code><\/b> clause.<\/p>\n

To use the OVER<\/code><\/b> clause, you first call the window function, followed by the OVER<\/code><\/b> keyword. You then specify one or more of the supported subclauses (enclosed in parentheses) to qualify your partitioning and ordering strategy. The OVER<\/code><\/b> clause supports three subclauses, as shown in the following syntax:<\/p>\n

<window function> OVER\n  (\n    [ PARTITION BY <expression> [, ... n] ]\n    [ ORDER BY <expression> [ASC|DESC] [, ... n] ]\n    [ ROWS|RANGE <window frame> ]\n  )\n\n<\/pre>\n
The PARTITION<\/code><\/b> BY<\/code><\/b> subclause partitions the result set based on one or more columns or expressions. In most cases, you’ll probably use a single column. All window functions support the PARTITION<\/code><\/b> BY<\/code><\/b> subclause, but it is optional in each case. When the subclause is not specified, the entire result set is treated as a single partition.<\/p>\n
The ORDER<\/code><\/b> BY<\/code><\/b> subclause sorts one or more columns or expressions within the partition. The ORDER<\/code><\/b> BY<\/code><\/b> clause applies strictly within the context of the partition. All ranking functions and most aggregate functions can use the ORDER<\/code><\/b> BY<\/code><\/b> subclause. For ranking functions, the subclause is required. For aggregate functions, the subclause is optional. Only certain analytic functions can use the ORDER<\/code><\/b> BY<\/code><\/b> subclause. However, those that can must use it.<\/p>\n
NOTE:<\/b> Most, but not all, aggregate functions support the OVER<\/code><\/b> clause. If they support the OVER<\/code><\/b> clause, then they support the ORDER<\/code><\/b> BY<\/code><\/b> subclause. Unfortunately, Microsoft documentation is a bit inconsistent on which ones support the OVER<\/code><\/b> clause and which do not. The easiest way to find out is to try them out, but know that the most common aggregate functions-such as SUM<\/code><\/b>, AVG<\/code><\/b>, MIN<\/code><\/b>, MAX<\/code><\/b>, and COUNT<\/code><\/b>-do support the clause.<\/p>\n
The ROWS\/RANGE<\/code><\/b> subclause further defines how the data is displayed within the partition. Ranking functions cannot use the ROWS\/RANGE<\/code><\/b> subclause. Aggregate functions can use the subclause, but it is optional. Only two analytic functions-FIRST_VALUE<\/code><\/b> and LAST_VALUE<\/code><\/b>-can use the subclause, and it is optional for them as well. <\/p>\n
The ORDER<\/code><\/b> BY<\/code><\/b> subclause must be included if the ROWS\/RANGE<\/code><\/b> subclause is specified. Also, if a window function supports the ROWS\/RANGE<\/code><\/b> subclause but the subclause is not defined, the function behaves as if the subclause is specified at its default value, which affects the behavior of the ORDER<\/code><\/b> BY<\/code><\/b> subclause. More on this later in the article.<\/p>\n
The details of how the subclauses fit together can seem a bit convoluted. The best way to understand how the various components work is to see them in action. The rest of the article uses examples to demonstrate these concepts. The examples are based on the table shown in the following T-SQL script:<\/p>\n
USE AdventureWorks2012;\nGO\n \nIF OBJECT_ID('RegionalSales', 'U') IS NOT NULL\nDROP TABLE RegionalSales;\nGO\n \nCREATE TABLE RegionalSales\n(\n  SalesID INT NOT NULL IDENTITY PRIMARY KEY,\n  SalesGroup NVARCHAR(30) NOT NULL,\n  Country NVARCHAR(30) NOT NULL,\n  AnnualSales INT NOT NULL\n);\nGO\n \nINSERT INTO RegionalSales \n  (SalesGroup, Country, AnnualSales)\nVALUES\n  ('North America', 'United States', 22000),\n  ('North America', 'Canada', 32000),\n  ('North America', 'Mexico', 28000),\n  ('Europe', 'France', 19000),\n  ('Europe', 'Germany', 22000),\n  ('Europe', 'Italy', 18000),\n  ('Europe', 'Greece', 16000),\n  ('Europe', 'Spain', 16000),\n  ('Europe', 'United Kingdom', 32000),\n  ('Pacific', 'Australia', 18000),\n  ('Pacific', 'China', 28000),\n  ('Pacific', 'Singapore', 21000),\n  ('Pacific', 'New Zealand', 18000),\n  ('Pacific', 'Thailand', 17000),\n  ('Pacific', 'Malaysia', 19000),\n  ('Pacific', 'Japan', 22000);\nGO\n \n<\/pre>\n
The script creates a simple table and populates the table with sales data broken into sales groups and countries. As for what the sales represent, use your imagination. They can be anything from hamburgers to paper airplanes to relational database management systems.<\/p>\n
Ranking Functions<\/h1>\n
Nothing has changed with the ranking functions in SQL Server 2012, and you might already be well versed in how they work. I’m including a brief overview here in order to be complete, but you can skip ahead if necessary, though you might find this section to be a handy refresher.<\/p>\n
The first example we’ll look at uses all four of the ranking functions to rank our sample sales data based on the amount of sales:<\/p>\n
SELECT\n  SalesGroup,\n  Country,\n  AnnualSales,\n  ROW_NUMBER() OVER(ORDER BY AnnualSales DESC) AS RowNumber,\n  RANK() OVER(ORDER BY AnnualSales DESC) AS BasicRank,\n  DENSE_RANK() OVER(ORDER BY AnnualSales DESC) AS DenseRank,\n  NTILE(3) OVER(ORDER BY AnnualSales DESC) AS NTileRank\nFROM\n  RegionalSales;\n \n<\/pre>\n
Notice that for each function, I include an ORDER<\/code><\/b> BY<\/code><\/b> subclause that sorts the AnnualSales<\/code><\/b> column in descending order. Because I have not included a PARTITION<\/code><\/b> BY<\/code><\/b> subclause, the result set is treated as a single partition. Each ranking function ranks the data, based on the sorted AnnualSales<\/code><\/b> values, in different ways. The following table shows the results returned by the SELECT<\/code><\/b> statement.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
Country<\/b><\/p>\n<\/td>\n
\n
AnnualSales<\/b><\/p>\n<\/td>\n
\n
RowNumber<\/b><\/p>\n<\/td>\n
\n
BasicRank<\/b><\/p>\n<\/td>\n
\n
DenseRank<\/b><\/p>\n<\/td>\n
\n
NTileRank<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Canada<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
United Kingdom<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Mexico<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
China<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
United States<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Japan<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Singapore<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Malaysia<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
9<\/p>\n<\/td>\n
\n
9<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
10<\/p>\n<\/td>\n
\n
9<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
11<\/p>\n<\/td>\n
\n
11<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Italy<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
12<\/p>\n<\/td>\n
\n
11<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
New Zealand<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
13<\/p>\n<\/td>\n
\n
11<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Thailand<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n
\n
14<\/p>\n<\/td>\n
\n
14<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Greece<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
15<\/p>\n<\/td>\n
\n
15<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Spain<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
16<\/p>\n<\/td>\n
\n
15<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 The ROW_NUMBER<\/code><\/b> function simply numbers each row in the partition. Because the statement returns 16 rows and there is only one partition, the rows are numbered 1<\/code><\/b> through 16<\/code><\/b>. <\/p>\n
The RANK<\/code><\/b> function also numbers each row consecutively, based on the sorted AnnualSales<\/code><\/b> values, but also takes into account duplicate values by assigning those values the same rank and then skipping the rank value that would have been assigned to the second duplicate. For example, the first two rows are ranked 1<\/code><\/b>, so the third row is ranked 3<\/code><\/b> because the 2<\/code><\/b> has been skipped.<\/p>\n
The DENSE_RANK<\/code><\/b> function takes a slightly different approach. It accounts for duplicates but doesn’t skip ranking values. Consequently, the first two rows receive a ranking value of 1<\/code><\/b>, as with RANK<\/code><\/b>, but the third row receives a value of 2<\/code><\/b>.<\/p>\n
The NTILE<\/code><\/b> function is a different animal all together. If you refer back to the SELECT<\/code><\/b> statement, you’ll notice that I passed a value of 3<\/code><\/b> in as an argument to the function. As a result, the function divides the partition into three groups. That division is based on the total number of rows divided by the number specified in the function’s argument. In this case, the 16 rows in the result set are divided into one group of six rows and two groups of five rows. The function assigns a 1<\/code><\/b> to each row in the first group, a 2<\/code><\/b> to each row in the second group, and a 3<\/code><\/b> to each row in the third group.<\/p>\n
You might have also noticed in the example above that the result set is sorted based on the AnnualSales<\/code><\/b> values (in descending order). However, that occurs only because we did not specify an ORDER<\/code><\/b> BY<\/code><\/b> clause on the outer statement itself. As you’ll recall, the ORDER<\/code><\/b> BY<\/code><\/b> subclause specified in the OVER<\/code><\/b> clause is specific to the partition, so SQL Server defaults to a sort order based on the subclause. But the subclause is specific to the function associated with the OVER<\/code><\/b> clause. That means, we can override the subclause’s sorting in the result set, without impacting the functions’ results.<\/p>\n
Let’s look at an example to demonstrate what that looks like. The following SELECT<\/code><\/b> statement is the same as the preceding one only now it includes an ORDER<\/code><\/b> BY<\/code><\/b> clause that sorts first by the SalesGroup<\/code><\/b> column and then by the Country<\/code><\/b> column:<\/p>\n
SELECT\n  SalesGroup,\n  Country,\n  AnnualSales,\n  ROW_NUMBER() OVER(ORDER BY AnnualSales DESC) AS RowNumber,\n  RANK() OVER(ORDER BY AnnualSales DESC) AS BasicRank,\n  DENSE_RANK() OVER(ORDER BY AnnualSales DESC) AS DenseRank,\n  NTILE(3) OVER(ORDER BY AnnualSales DESC) AS NTileRank\nFROM\n  RegionalSales\nORDER BY\n  SalesGroup, Country;\n \n<\/pre>\n
As you can see, nothing has changed but the addition of the ORDER<\/code><\/b> BY<\/code><\/b> clause. However, as the following table shows, the results in the ranking columns are in a different order:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
Country<\/b><\/p>\n<\/td>\n
\n
AnnualSales<\/b><\/p>\n<\/td>\n
\n
RowNumber<\/b><\/p>\n<\/td>\n
\n
BasicRank<\/b><\/p>\n<\/td>\n
\n
DenseRank<\/b><\/p>\n<\/td>\n
\n
NTileRank<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
10<\/p>\n<\/td>\n
\n
9<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Greece<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
15<\/p>\n<\/td>\n
\n
15<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Italy<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
12<\/p>\n<\/td>\n
\n
11<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Spain<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
16<\/p>\n<\/td>\n
\n
15<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
United Kingdom<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Canada<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Mexico<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
United States<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
11<\/p>\n<\/td>\n
\n
11<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
China<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Japan<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Malaysia<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
9<\/p>\n<\/td>\n
\n
9<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
New Zealand<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
13<\/p>\n<\/td>\n
\n
11<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Singapore<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Thailand<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n
\n
14<\/p>\n<\/td>\n
\n
14<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The data in this result set is the same as the one in the preceding example. It’s just messier looking. For instance, the RowNumber<\/code><\/b> value for France is 10<\/code><\/b>, the BasicRank<\/code><\/b> value is 9<\/code><\/b>, the DenseRank<\/code><\/b> value is 5<\/code><\/b>, and the NTileRank<\/code><\/b> value is 2<\/code><\/b>, just as they were in the preceding example. However, because we specifically sorted our entire result set, the ranking columns themselves are no longer in order, but they still base their data on the sorted AnnualSales<\/code><\/b> column, as defined in the ORDER<\/code><\/b> BY<\/code><\/b> subclause of the OVER<\/code><\/b> clause. What this points to is that the ORDER<\/code><\/b> BY<\/code><\/b> subclause is specific to the window function associated with the OVER<\/code><\/b> clause.<\/p>\n
Now let’s return to our original example, only this time we’ll add a PARTITION<\/code><\/b> BY<\/code><\/b> subclause to each rank:<\/p>\n
SELECT\n  SalesGroup,\n  Country,\n  AnnualSales,\n  ROW_NUMBER() OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS RowNumber,\n  RANK() OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS BasicRank,\n  DENSE_RANK() OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS DenseRank,\n  NTILE(3) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS NTileRank\nFROM\n  RegionalSales;\n \n<\/pre>\n
For each ranking function, we partition the results by the SalesGroup<\/code><\/b> values, which breaks our result set into three distinct groups. The following table shows the results now returned by the SELECT<\/code><\/b> statement:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
Country<\/b><\/p>\n<\/td>\n
\n
AnnualSales<\/b><\/p>\n<\/td>\n
\n
RowNumber<\/b><\/p>\n<\/td>\n
\n
BasicRank<\/b><\/p>\n<\/td>\n
\n
DenseRank<\/b><\/p>\n<\/td>\n
\n
NTileRank<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
United Kingdom<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Italy<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Greece<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Spain<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Canada<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Mexico<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
United States<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
China<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Japan<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Singapore<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Malaysia<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
New Zealand<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Thailand<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 As you can see, the ranking functions now each apply to the individual partitions, which are based on the sales group. For example, the Europe sales group includes six rows, one for each country. As a result, the ROW_NUMBER<\/code><\/b> function ranks the rows 1<\/code><\/b> through 6<\/code><\/b>.  However, because the last two values in that group are duplicates, the RANK<\/code><\/b> and DENSE_RANK<\/code><\/b> functions assign the first four rows 1<\/code><\/b> through 4<\/code><\/b> and assign a 5<\/code><\/b> to the last two rows. The RANK<\/code><\/b> function doesn’t skip any numbers because the duplicates come at the end of the sorted AnnualSales<\/code><\/b> column. And when the NTILE<\/code><\/b> function breaks the Europe partition into three groups, each group contains only three rows.<\/p>\n
Now let’s see what happens if we add an ORDER<\/code><\/b> BY<\/code><\/b> clause to the statement itself, as shown in the following example:<\/p>\n
SELECT\n  SalesGroup,\n  Country,\n  AnnualSales,\n  ROW_NUMBER() OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS RowNumber,\n  RANK() OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS BasicRank,\n  DENSE_RANK() OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS DenseRank,\n  NTILE(3) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS NTileRank\nFROM\n  RegionalSales\nORDER BY\n  SalesGroup, Country;\n \n<\/pre>\n
Once again, I’ve sorted the result set first by SalesGroup<\/code><\/b> and then by Country<\/code><\/b>. As the following table demonstrates, the ranking values are now displayed in a different order:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
Country<\/b><\/p>\n<\/td>\n
\n
AnnualSales<\/b><\/p>\n<\/td>\n
\n
RowNumber<\/b><\/p>\n<\/td>\n
\n
BasicRank<\/b><\/p>\n<\/td>\n
\n
DenseRank<\/b><\/p>\n<\/td>\n
\n
NTileRank<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Greece<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Italy<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Spain<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
United Kingdom<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Canada<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Mexico<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
United States<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
China<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Japan<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Malaysia<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
New Zealand<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Singapore<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Thailand<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 Although the result set itself has been sorted, the ranking data is still based on the defined partition and sort order within the partition. Each country receives the same ranking values as they did in the earlier example, only now they’re in a different order.<\/p>\n
Aggregate Functions<\/h1>\n
Aggregate functions work a bit differently from ranking functions, not only because of the functions themselves, but also in terms of how the subclauses work. For example, aggregate functions don’t require an ORDER<\/code><\/b> BY<\/code><\/b> subclause and can include a ROWS\/RANGE<\/code><\/b> subclause. But first, let’s look at aggregate functions that use only the PARTITION<\/code><\/b> BY<\/code><\/b> subclause. In the following example, the SELECT<\/code><\/b> statement uses several aggregate functions to calculate AnnualSales<\/code><\/b> values for each partition:<\/p>\n
SELECT \n  SalesGroup,\n  Country,\n  AnnualSales,\n  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup) AS CountryCount,\n  SUM(AnnualSales) OVER(PARTITION BY SalesGroup) AS TotalSales,\n  AVG(AnnualSales) OVER(PARTITION BY SalesGroup) AS AverageSales\nFROM\n  RegionalSales\nORDER BY \n  SalesGroup, AnnualSales DESC;\n \n<\/pre>\n
As before the statement partitions the data by sales group. In addition, for each function, I pass in the AnnualSales<\/code><\/b> column as the argument because I want to perform my calculations on that column. The SELECT<\/code><\/b> statement returns the results shown in the following table:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
Country<\/b><\/p>\n<\/td>\n
\n
AnnualSales<\/b><\/p>\n<\/td>\n
\n
CountryCount<\/b><\/p>\n<\/td>\n
\n
TotalSales<\/b><\/p>\n<\/td>\n
\n
AverageSales<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
United Kingdom<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
123000<\/p>\n<\/td>\n
\n
20500<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
123000<\/p>\n<\/td>\n
\n
20500<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
123000<\/p>\n<\/td>\n
\n
20500<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Italy<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
123000<\/p>\n<\/td>\n
\n
20500<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Greece<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
123000<\/p>\n<\/td>\n
\n
20500<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Spain<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
123000<\/p>\n<\/td>\n
\n
20500<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Canada<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
82000<\/p>\n<\/td>\n
\n
27333<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Mexico<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
82000<\/p>\n<\/td>\n
\n
27333<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
United States<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
82000<\/p>\n<\/td>\n
\n
27333<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
China<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
143000<\/p>\n<\/td>\n
\n
20428<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Japan<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
143000<\/p>\n<\/td>\n
\n
20428<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Singapore<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
143000<\/p>\n<\/td>\n
\n
20428<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Malaysia<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
143000<\/p>\n<\/td>\n
\n
20428<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
143000<\/p>\n<\/td>\n
\n
20428<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
New Zealand<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
143000<\/p>\n<\/td>\n
\n
20428<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Thailand<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
143000<\/p>\n<\/td>\n
\n
20428<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Notice how the aggregate functions have been applied to each group. For example, the COUNT<\/code><\/b> function returns a 6<\/code><\/b> for each row in the Europe group, the SUM<\/code><\/b> function returns 123000<\/code><\/b> for each row in that group, and the AVG<\/code><\/b> function returns 20500<\/code><\/b> for those rows.<\/p>\n
If you wanted to get at the aggregate calculations only, you can simplify your statement in order to eliminate duplicate values, as shown in the following example:<\/p>\n
SELECT DISTINCT\n  SalesGroup,\n  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup) AS CountryCount,\n  SUM(AnnualSales) OVER(PARTITION BY SalesGroup) AS TotalSales,\n  AVG(AnnualSales) OVER(PARTITION BY SalesGroup) AS AverageSales\nFROM\n  RegionalSales\nORDER BY \n  TotalSales DESC;\n \n<\/pre>\n
Now the SELECT<\/code><\/b> statement returns only the data we need:<\/p>\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
CountryCount<\/b><\/p>\n<\/td>\n
\n
TotalSales<\/b><\/p>\n<\/td>\n
\n
AverageSales<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
143000<\/p>\n<\/td>\n
\n
20428<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
123000<\/p>\n<\/td>\n
\n
20500<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
82000<\/p>\n<\/td>\n
\n
27333<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 So far, everything we’ve looked at to this point has existed in SQL Server since the 2005 release. However, SQL Server 2012 now supports the ORDER<\/code><\/b> BY<\/code><\/b> subclause for aggregate functions. For example, the following SELECT<\/code><\/b> statement adds an ORDER<\/code><\/b> BY<\/code><\/b> subclause that sorts AnnualSales<\/code><\/b> in descending order:<\/p>\n
SELECT \n  SalesGroup,\n  Country,\n  AnnualSales,\n  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS CountryCount,\n  SUM(AnnualSales) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS TotalSales,\n  AVG(AnnualSales) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS AverageSales\nFROM\n  RegionalSales;\n \n<\/pre>\n
The data is still partitioned by the SalesGroup<\/code><\/b> column, only now we’ve added the ORDER<\/code><\/b> BY<\/code><\/b> subclause. However, as the following table shows, the statement’s results might not be quite what you expect:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
Country<\/b><\/p>\n<\/td>\n
\n
AnnualSales<\/b><\/p>\n<\/td>\n
\n
CountryCount<\/b><\/p>\n<\/td>\n
\n
TotalSales<\/b><\/p>\n<\/td>\n
\n
AverageSales<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
United Kingdom<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
54000<\/p>\n<\/td>\n
\n
27000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
73000<\/p>\n<\/td>\n
\n
24333<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Italy<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
91000<\/p>\n<\/td>\n
\n
22750<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Greece<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
123000<\/p>\n<\/td>\n
\n
20500<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Spain<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
123000<\/p>\n<\/td>\n
\n
20500<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Canada<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Mexico<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
60000<\/p>\n<\/td>\n
\n
30000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
United States<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
82000<\/p>\n<\/td>\n
\n
27333<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
China<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Japan<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
50000<\/p>\n<\/td>\n
\n
25000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Singapore<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
71000<\/p>\n<\/td>\n
\n
23666<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Malaysia<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
90000<\/p>\n<\/td>\n
\n
22500<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
126000<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
New Zealand<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n
\n
126000<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Thailand<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n
\n
7<\/p>\n<\/td>\n
\n
143000<\/p>\n<\/td>\n
\n
20428<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 In fact, when you use the ORDER<\/code><\/b> BY<\/code><\/b> subclause with an aggregate function, the aggregated data changes with each row. The result set now shows moving averages and cumulative totals. For example, Germany shows a count of 2<\/code><\/b>, a total of 54000<\/code><\/b>, and an average of 27000<\/code><\/b>. Because Germany is the second row in the partition, as determined by the ORDER<\/code><\/b> BY<\/code><\/b> subclause, the aggregated totals reflect only those two rows. The same thing goes for France. Because it comes in at number 3<\/code><\/b>, the aggregated totals reflect only the first three rows. To complicate matters, duplicated rows are grouped together, as is the case with Greece and Spain.<\/p>\n
There’s a reason for this behavior. As you’ll recall from earlier in the article, when a window function supports the ROWS\/RANGE<\/code><\/b> subclause but the subclause has not been specified, the function operates as if it has been specified with its default value. And that default setting impacts the ORDER<\/code><\/b> BY <\/code><\/b>subclause. <\/p>\n
The default setting for the ROWS\/RANGE<\/code><\/b> subclause is RANGE<\/code><\/b> BETWEEN<\/code><\/b> UNBOUNDED<\/code><\/b> PRECEDING<\/code><\/b> AND<\/code><\/b> CURRENT<\/code><\/b> ROW<\/code><\/b>. This means that, for each row in the partition, the window function is applied to the current row and the preceding rows only. So aggregations don’t operate on the entire set of values within the partition, but only on the value in the current row and the previous rows, as we saw in the example above.<\/p>\n
NOTE:<\/b> The main difference between a ROWS<\/code><\/b> clause and a RANGE<\/code><\/b> clause is in the way duplicate data is treated. ROWS<\/code><\/b> treats duplicates as distinct values. RANGE<\/code><\/b> treats them as a single entity, as the above result set indicates.<\/p>\n
The way to get around the default behavior, of course, is to add a ROWS\/RANGE<\/code><\/b> subclause that overrides that behavior, as shown in the following example:<\/p>\n
SELECT \n  SalesGroup,\n  Country,\n  AnnualSales,\n  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC \n      ROWS 2 PRECEDING) AS CountryCount,\n  SUM(AnnualSales) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC \n      ROWS 2 PRECEDING) AS TotalSales,\n  AVG(AnnualSales) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC \n      ROWS 2 PRECEDING) AS AverageSales\nFROM\n  RegionalSales;\n \n<\/pre>\n
Notice that I’ve added the subclause ROWS<\/code><\/b> 2<\/code><\/b> PRECEDING<\/code><\/b> to each instance of the OVER<\/code><\/b> clause. Now for each partition, the aggregate function applies only to the current row and the two preceding rows, as shown in the following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
Country<\/b><\/p>\n<\/td>\n
\n
AnnualSales<\/b><\/p>\n<\/td>\n
\n
CountryCount<\/b><\/p>\n<\/td>\n
\n
TotalSales<\/b><\/p>\n<\/td>\n
\n
AverageSales<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
United Kingdom<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
54000<\/p>\n<\/td>\n
\n
27000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
73000<\/p>\n<\/td>\n
\n
24333<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Italy<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
59000<\/p>\n<\/td>\n
\n
19666<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Greece<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
53000<\/p>\n<\/td>\n
\n
17666<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Spain<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
50000<\/p>\n<\/td>\n
\n
16666<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Canada<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Mexico<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
60000<\/p>\n<\/td>\n
\n
30000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
United States<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
82000<\/p>\n<\/td>\n
\n
27333<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
China<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
1<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Japan<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
50000<\/p>\n<\/td>\n
\n
25000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Singapore<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
71000<\/p>\n<\/td>\n
\n
23666<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Malaysia<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
62000<\/p>\n<\/td>\n
\n
20666<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
58000<\/p>\n<\/td>\n
\n
19333<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
New Zealand<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
55000<\/p>\n<\/td>\n
\n
18333<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Thailand<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n
\n
3<\/p>\n<\/td>\n
\n
53000<\/p>\n<\/td>\n
\n
17666<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 Because the ROWS<\/code><\/b> subclause is included, each aggregated value never calculates more than three rows, so the CountryCount<\/code><\/b> value will never exceed 3<\/code><\/b> and the total and average sales will never represent the total amounts within the group, unless that group has fewer than four rows. If you don’t want to aggregate your partitioned data in this way-with totals and averages that don’t reflect the entire partition-your best bet is to go with the PARTITION<\/code><\/b> BY<\/code><\/b> subclause only and not the other subclauses.<\/p>\n
Analytic Functions<\/h1>\n
Analytic functions work much the same way as aggregate functions, except that the ORDER<\/code><\/b> BY<\/code><\/b> subclause is required for those functions that support the clause. In our next example, we’ll try out the FIRST_VALUE<\/code><\/b> and LAST_VALUE<\/code><\/b> analytic functions. The FIRST_VALUE<\/code><\/b> function retrieves the first value from a sorted list, and the LAST_VALUE<\/code><\/b> function retrieves the last value. In the following example, the SELECT<\/code><\/b> statement uses both of these functions to calculate AnnualSales<\/code><\/b> values in each sales group:<\/p>\n
SELECT \n  SalesGroup,\n  Country,\n  AnnualSales,\n  FIRST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS HighestSales,\n  LAST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS LowestSales\nFROM\n  RegionalSales;\n \n<\/pre>\n
Once again, I use the PARTITION<\/code><\/b> BY<\/code><\/b> subclause to partition the result set by sales group. In addition, I use the ORDER<\/code><\/b> BY<\/code><\/b> subclause to specify that the AnnualSales<\/code><\/b> values be sorted in descending order. The following table shows the results returned by the SELECT<\/code><\/b> statement.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
Country<\/b><\/p>\n<\/td>\n
\n
AnnualSales<\/b><\/p>\n<\/td>\n
\n
HighestSales<\/b><\/p>\n<\/td>\n
\n
LowestSales<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
United Kingdom<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Italy<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Greece<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Spain<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Canada<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Mexico<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
United States<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
China<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Japan<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Singapore<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Malaysia<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
New Zealand<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Thailand<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 As to be expected, the result set is grouped by the SalesGroup<\/code><\/b> column, with the AnnualSales<\/code><\/b> values in each group sorted. The HighestSales<\/code><\/b> column displays the first of the sorted values, and the LowestSales<\/code><\/b> column displays the last of the sorted values. But these values are running totals because the FIRST_VALUE<\/code><\/b> and LAST_VALUE<\/code><\/b> functions support the ROWS\/RANGE<\/code><\/b> subclause, which impacts the ORDER<\/code><\/b> BY<\/code><\/b> operation. For example, the highest amount of sales for the France row is 32000<\/code><\/b> and the lowest amount is 19000<\/code><\/b>. These calculations are based only on the first three rows in this partition as a result of the ROWS\/RANGE<\/code><\/b> default settings being applied.<\/p>\n
As before, we can override the subclause’s default behavior. Only this time, let’s change it to incorporate all AnnualSales<\/code><\/b> in each calculation, regardless of row:<\/p>\n
SELECT \n  SalesGroup,\n  Country,\n  AnnualSales,\n  FIRST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS HighestSales,\n  LAST_VALUE(AnnualSales) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC\n      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) \n      AS LowestSales\nFROM\n  RegionalSales;\n \n<\/pre>\n
Notice that our ROWS<\/code><\/b> clause specifies unbounded preceding and following values, which means all values should be included in the calculations. The following table shows what the results look like:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
Country<\/b><\/p>\n<\/td>\n
\n
AnnualSales<\/b><\/p>\n<\/td>\n
\n
HighestSales<\/b><\/p>\n<\/td>\n
\n
LowestSales<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
United Kingdom<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Italy<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Greece<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Spain<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Canada<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Mexico<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
United States<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
China<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Japan<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Singapore<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Malaysia<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
New Zealand<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Thailand<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 As you can see, the HighestSales<\/code><\/b> and LowestSales<\/code><\/b> columns now display duplicate values for each row in a sales group, providing both the first and last value, respectively, in the sorted AnnualSales<\/code><\/b> column. You can, of course, simplify your SELECT<\/code><\/b> statement to retrieve only the necessary distinct values, as we did in an earlier example.<\/p>\n
Now let’s look at two other analytic functions: LAG<\/code><\/b> and LEAD<\/code><\/b>. The LEAD<\/code><\/b> function retrieves a value from a row previous to the current one. The LAG<\/code><\/b> function retrieves a value from a row after the current one. The following SELECT<\/code><\/b> statement demonstrates how to use these functions:<\/p>\n
SELECT \n  SalesGroup,\n  Country,\n  AnnualSales,\n  LAG(AnnualSales, 1) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS PreviousSale,\n  LEAD(AnnualSales, 1) OVER(PARTITION BY SalesGroup\n    ORDER BY AnnualSales DESC) AS NextSale\nFROM\n  RegionalSales;\n \n<\/pre>\n
First, notice that we pass a second argument into the functions, in this case, 1<\/code><\/b>. The argument indicates that we go one row up or down to retrieve the value from the sorted AnnualSales<\/code><\/b> values. The following table shows the results returned by the SELECT<\/code><\/b> statement.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
SalesGroup<\/b><\/p>\n<\/td>\n
\n
Country<\/b><\/p>\n<\/td>\n
\n
AnnualSales<\/b><\/p>\n<\/td>\n
\n
PreviousSale<\/b><\/p>\n<\/td>\n
\n
NextSale<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
United Kingdom<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Germany<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
France<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Italy<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Greece<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n<\/tr>\n
\n
Europe<\/p>\n<\/td>\n
\n
Spain<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
16000<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Canada<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
Mexico<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
32000<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n<\/tr>\n
\n
North America<\/p>\n<\/td>\n
\n
United States<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
China<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Japan<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
28000<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Singapore<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
22000<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Malaysia<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
21000<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Australia<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
19000<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
New Zealand<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n<\/tr>\n
\n
Pacific<\/p>\n<\/td>\n
\n
Thailand<\/p>\n<\/td>\n
\n
17000<\/p>\n<\/td>\n
\n
18000<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 As the results show, each row displays AnnualSales<\/code><\/b> values from the previous and next rows within each partition, unless it’s a first or last row, in which case NULL<\/code><\/b> is returned. For example, the United Kingdom row returns a NULL<\/code><\/b> in the PreviousSale<\/code><\/b> column because no rows precede this row. However, the NextSale<\/code><\/b> column displays the amount 22000<\/code><\/b>, the AnnualSales<\/code><\/b> amount from the row that follows.<\/p>\n
SQL Server supports four other analytic functions that mostly have to do with calculating percentages. It also supports additional ways to configure the ROWS\/RANGE<\/code><\/b> subclause. In fact, the subclause supports a number of variations. But the key to understanding the subclause is to become familiar with the OVER<\/code><\/b> clause and how the subclauses work together to partition and present data. For a complete discussion of the clause, see the topic “OVER Clause (Transact-SQL)<\/a>” in SQL Server Books Online. And be willing to try out window functions in different scenarios. They’re the type of feature you need to mess with for a while before you become fully comfortable with using them. <\/p>\n
<\/p>\n
<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
For some time, Microsoft had a few window functions, but not the full set specified in the SQL 2003 standard. Now, in SQL Server 2012 we have the whole range, and extremely useful they are too. There’s no longer an excuse to avoid them, particularly now you have Rob’s gentle introduction.…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4242,4150,4151,4252],"coauthors":[],"class_list":["post-1598","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-basics","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1598","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1598"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1598\/revisions"}],"predecessor-version":[{"id":92532,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1598\/revisions\/92532"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1598"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1598"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1598"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1598"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}