{"id":1225,"date":"2011-10-27T00:00:00","date_gmt":"2011-10-27T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/window-functions-in-sql-server\/"},"modified":"2021-08-16T15:02:03","modified_gmt":"2021-08-16T15:02:03","slug":"window-functions-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/window-functions-in-sql-server\/","title":{"rendered":"Window Functions in SQL Server"},"content":{"rendered":"
\n

Hi Folks, I’m going to start a new series of three articles about Window functions, starting off by explaining what they are, and how they are used to provide details of an aggregation.<\/p>\n

Window functions<\/h2>\n

Window functions belong to a type of function known as a ‘set function’, which means a function that applies to a set of rows. The word ‘window’ is used to refer to the set of rows that the function works on.<\/p>\n

Windowing functions were added to the standard SQL:2003<\/a> that is managed by the ISO and it was specified in more detail in SQL:2008<\/a> For some time, other DBMSs such as Oracle, Sybase and DB2 have had support for window functions. Even the open source RDBMS PostgreSQL has a full implementation. SQL Server has had only a partial implementation up to now, but it is coming in SQL 2012.<\/p>\n

One of the most important benefits of window functions is that we can access the detail of the rows from an aggregation. To see an example, let’s first suppose we have this table and data:<\/p>\n

USE tempdb\r\nGO\r\nIF OBJECT_ID('TestAggregation') IS NOT NULL\r\n\u00a0\u00a0DROP TABLE TestAggregation\r\nGO\r\nCREATE TABLE TestAggregation (ID INT, Value Numeric(18,2))\r\nGO\r\n\r\nINSERT INTO TestAggregation (ID, Value) \r\nVALUES(1, 50.3), (1, 123.3), (1, 132.9),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(2, 50.3), (2, 123.3), (2, 132.9), (2, 88.9),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(3, 50.3), (3, 123.3);\r\nGO<\/pre>\n
This is what the data looks like:<\/p>\n
SELECT * FROM TestAggregation<\/pre>\n
\"1378-image001.png\"<\/p>\n
If we sum the column Value grouping by ID, by using a conventional GROUP BY, we would have the following query and result:<\/p>\n
SELECT ID, SUM(Value)\r\n\u00a0\u00a0FROM TestAggregation\r\nGROUP BY ID;<\/pre>\n
\"1378-image002.png\"<\/p>\n
Here we can see a sample of the partitions of rows, or sets that the SUM aggregation function is working on.<\/p>\n
\"1378-image003.png\"<\/p>\n
In the blue we have partition 1, green as partition 2 and red as partition 3. Because we applied the aggregation function in the column value, grouping the results by ID, we the lose the details of the data. In this case the details are the values of the columns of the rows in the partitions 1, 2 and 3.<\/p>\n
Let’s suppose I need to write a query to return the total value of sales, the average value of sales and the quantity of sales for each ID, and still return the actual values of the rows, then we might think that we could use something like this to return this data:<\/p>\n
SELECT ID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Value,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(Value) AS \"Sum\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AVG(Value) AS \"Avg\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(Value) AS \"Quantity\"\u00a0\u00a0FROM TestAggregation\r\nGROUP BY ID;<\/pre>\n
Msg 8120, Level 16, State 1, Line 2<\/p>\n
Column ‘TestAggregation.Value’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.<\/p>\n
Unfortunately it is against the way that aggregations work. If you group by something, then you lose access to the details.<\/p>\n
A very commonly used alternative is to write every aggregation into a subquery, and then correlate with the main query using a join, something like:<\/p>\n
SELECT TestAggregation.ID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TestAggregation.Value,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TabSum.\"Sum\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TabAvg.\"Avg\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TabCount.\"Quantity\"\u00a0\u00a0\r\n\u00a0\u00a0FROM TestAggregation\r\n\u00a0INNER JOIN (SELECT ID, SUM(Value) AS \"Sum\"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM TestAggregation\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0GROUP BY ID) AS TabSum\r\n\u00a0\u00a0\u00a0\u00a0ON TabSum.ID = TestAggregation.ID\r\n\u00a0INNER JOIN (SELECT ID, AVG(Value) AS \"Avg\"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM TestAggregation\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0GROUP BY ID) AS TabAvg\r\n\u00a0\u00a0\u00a0\u00a0ON TabAvg.ID = TestAggregation.ID\r\n\u00a0INNER JOIN (SELECT ID, COUNT(Value) AS \"Quantity\"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM TestAggregation\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0GROUP BY ID) AS TabCount\r\n\u00a0\u00a0\u00a0\u00a0ON TabCount.ID = TestAggregation.ID<\/pre>\n
\"1378-image004.png\"<\/p>\n
But a neater and faster solution is this…<\/p>\n
SELECT\r\n\u00a0\u00a0TestAggregation.ID,\r\n\u00a0\u00a0TestAggregation.Value,\r\n\u00a0\u00a0AggregatedValues.[Sum],\r\n\u00a0\u00a0AggregatedValues.[Avg],\r\n\u00a0\u00a0AggregatedValues.Quantity\r\nFROM\u00a0\u00a0 TestAggregation\r\nINNER JOIN \r\n\u00a0\u00a0(\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0SELECT ID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(Value) AS \"Sum\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AVG(Value) AS \"Avg\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(Value) AS \"Quantity\"\u00a0\u00a0\u00a0\u00a0FROM TestAggregation\r\n\u00a0\u00a0 GROUP BY ID) AggregatedValues\r\nON AggregatedValues.ID=TestAggregation.ID<\/pre>\n
A very elegant alternative is to use the clause OVER() implemented in the aggregation functions, it allow-us to access the details of the rows that have been aggregated.<\/p>\n
For instance we could try to get the result we want with this …<\/p>\n
SELECT ID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Value,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(Value) OVER() AS \"Sum\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AVG(Value) OVER() AS \"Avg\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(Value) OVER() AS \"Quantity\"\u00a0\u00a0FROM TestAggregation<\/pre>\n
\"1378-image005.png\"<\/p>\n
…but, with this query, we didn’t return the expected results. In fact, we returned the aggregate values for the entire table rather than for each ID. Using the clause OVER() with the aggregation function (SUM, AVG and COUNT) we have accessed the details of the grouped data along with a total aggregation of the data, but, in In fact, we want the aggregates for the data grouped by ID. To do this, we should use the clause PARTITION BY clause. For instance:<\/p>\n
SELECT ID,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Value,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(Value) OVER(PARTITION BY ID) AS \"Sum\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AVG(Value) OVER(PARTITION BY ID) AS \"Avg\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(Value) OVER(PARTITION BY ID) AS \"Quantity\"\r\n\u00a0\u00a0FROM TestAggregation<\/pre>\n
\"1378-image004.png\"<\/p>\n
Now we can see the same results as in the subquery alternative, but with a much more simple and elegant code.<\/p>\n
In the following picture we can see that even the results are grouped by ID we can see access the details of the aggregated partitions through the clause OVER and PARTITION BY.<\/p>\n
\"1378-image007.jpg\"<\/p>\n
Set based thinking<\/h2>\n
It can get tedious to hear about this set base thing, but in fact it’s not so easy to think set based. Sometimes it’s very hard to find a set based solution to a query we have. Window Functions give us more set-based solutions to awkward problems.<\/p>\n
The main point of windowing functions it’s that they were created to work with a set. SQL Server never was good on processing queries row by row, that’s why you always hearing that ‘cursors are evil’, and are ‘not good for performance’, ‘you should avoid them’ and so on. SQL Server was not built to work row by row.<\/p>\n
Let’s illustrate this. SQL Server takes an average of 50 seconds to run a loop of 1 hundred million times against 100 milliseconds of a Win32 app.<\/p>\n
DECLARE @i INT = 0, @Time Time(3) = GETDATE()\r\nWHILE @i < 100000000\r\nBEGIN\r\n\u00a0\u00a0SET @i += 1;\r\nEND\r\n\r\nSELECT CONVERT(Time(3), GETDATE() - @Time) AS \"Total Time to run the Loop\"<\/pre>\n
\"1378-image008.png\"<\/p>\n
Delphi Code:<\/p>\n
PROCEDURE TForm1.Button1Click(Sender: TObject);\r\nVar\r\n\u00a0\u00a0i : Integer;\r\n\u00a0\u00a0Tempo : TDateTime;\r\nBEGIN\r\n\u00a0\u00a0i := 0;\r\n\u00a0\u00a0Tempo := Now();\r\n\u00a0\u00a0WHILE i < 100000000 do\r\n\u00a0\u00a0BEGIN\r\n\u00a0\u00a0\u00a0\u00a0inc(i);\r\n\u00a0\u00a0END;<\/pre>\n
\"1378-image009.png\"<\/p>\n
Of course it is an unfair comparison. The compiler of a win32 application is totally different from SQL Server, what I wanted to show here is the fact that SQL Server was not supposed to run row by row.<\/p>\n
I once was in London doing training with my colleague Itzik Ben-Gan when I remember he said: “There is no row-by-row code that you cannot run an equivalent set-based version, the problem is that you didn’t figured out how to do it”. Yes it’s a heavy phrase but, who could tell Itzik that this is not true? Not I!<\/p>\n
Window functions on SQL Server 2005 and 2008<\/h2>\n
Since SQL Server 2005 we have had support for some window functions, they are: ROW_NUMBER, RANK, DENSE_RANK and NTILE.<\/p>\n
In this first article we’ll review how these functions works, and how they can help-us to write better and efficient set-based codes.<\/p>\n
Test database<\/p>\n
To test the functions we’ll use a table called Tab1. The code to create the table is the following:<\/p>\n
USE TempDB\r\nGO\r\nIF OBJECT_ID('Tab1') IS NOT NULL\r\n\u00a0\u00a0DROP TABLE Tab1\r\nGO\r\nCREATE TABLE Tab1 (Col1 INT)\r\nGO\r\n\r\nINSERT INTO Tab1 VALUES(5), (5), (3) , (1)\r\nGO<\/pre>\n
Row_Number()<\/h3>\n
The ROW_NUMBER function is used to generate a sequence of numbers based in a set in a specific order, in easy words, it returns the sequence number of each row inside a set in the order that you specify.<\/p>\n
For instance:<\/p>\n
-- RowNumber\r\nSELECT Col1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ROW_NUMBER() OVER(ORDER BY Col1 DESC) AS \"ROW_NUMBER()\"\u00a0\u00a0\r\n\u00a0\u00a0FROM Tab1<\/pre>\n
\"1378-image010.png\"<\/p>\n
The column called “ROW_NUMBER()” is one of a series of numbers created in the order of Col1 descending. The clause OVER(ORDER BY Col1 DESC) is used to specify the order of the sequence for which the number should be created. It is necessary because rows in a relational table have no ‘natural’ order.<\/p>\n
Rank() & Dense_Rank()<\/h3>\n
Return the position in a ranking for each row inside a partition. The ranking is calculated by 1 plus the number of previews rows.<\/p>\n
It’s important to mention that the function RANK returns the result with a GAP after a tie, whereas the function DENSE_RANK doesn’t. To understand this better, let’s see some samples.<\/p>\n
-- Rank\r\nSELECT Col1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RANK() OVER(ORDER BY Col1 DESC) AS \"RANK()\"\u00a0\u00a0FROM Tab1\r\nGO\r\n\r\n-- Dense_Rank\r\nSELECT Col1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DENSE_RANK() OVER(ORDER BY Col1 DESC) AS \"DENSE_RANK\"\u00a0\u00a0FROM Tab1<\/pre>\n
\"1378-image011.png\"<\/p>\n
Notice that in the RANK result, we have the values 1,1,3 and 4. The value Col1 = “5” is duplicated so any ordering will produce a ‘tie’ for position between them. They have the same position in the rank, but, when the ordinal position for the value 3 is calculated, this position isn’t 2 because the position 2 was already used for the value 5, in this case the an GAP is generated and the function returns the next value for the tank, in this case the value 3.<\/p>\n
NTILE()<\/h3>\n
The NTILE function is used for calculating summary statistics. It distributes the rows within an ordered partition into a specified number of “buckets” or groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs. It makes it easy to calculate n-tile distributions such as percentiles.<\/p>\n
Let’s see a sample:<\/p>\n
-- NTILE\r\nSELECT Col1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NTILE(3) OVER(ORDER BY Col1 DESC) AS \"NTILE(3)\"\r\n\u00a0\u00a0FROM Tab1<\/pre>\n
\"1378-image012.png\"<\/p>\n
In the result above we can see that 4 rows were divided by 3 it’s 1, the remaining row is added in the initial group. Let’s see another sample without remained rows.<\/p>\n
-- NTILE\r\nSELECT Col1, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 NTILE(2) OVER(ORDER BY Col1 DESC) AS \"NTILE(2)\"\r\n\u00a0\u00a0FROM Tab1<\/pre>\n
\"1378-image013.png\"<\/p>\n
It is good practice to order on a unique key, ensure that there are more buckets than rows, and to have an equal number of rows in each bucket<\/p>\n
The Power of Window functions<\/h2>\n
Now let’s see some examples where window functions could be used to return some complex queries.<\/p>\n
Example 1<\/h3>\n
Let’s suppose I need to write a query to return those employees that receive more than his department’s average.<\/p>\n
Let’s start by creating two sample tables called Departamentos (departments in Portuguese) and Funcionarios (employees in Portuguese):<\/p>\n
IF OBJECT_ID('Departamentos') IS NOT NULL\r\n\u00a0\u00a0DROP TABLE Departamentos\r\nGO\r\nCREATE TABLE Departamentos (ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INT IDENTITY(1,1) PRIMARY KEY,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Nome_Dep VARCHAR(200))\r\nGO\r\n\r\nINSERT INTO Departamentos(Nome_Dep) \r\nVALUES('Vendas'), ('TI'), ('Recursos Humanos')\r\nGO\r\n\r\nIF OBJECT_ID('Funcionarios') IS NOT NULL\r\n\u00a0\u00a0DROP TABLE Funcionarios\r\nGO\r\nCREATE TABLE Funcionarios (ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INT IDENTITY(1,1) PRIMARY KEY,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ID_Dep\u00a0\u00a0INT,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Nome\u00a0\u00a0\u00a0\u00a0VARCHAR(200),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Salario Numeric(18,2))\r\nGO\r\n\r\nINSERT INTO Funcionarios (ID_Dep, Nome, Salario) \r\nVALUES(1, 'Fabiano', 2000), (1, 'Amorim', 2500), (1, 'Diego', 9000),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(2, 'Felipe', 2000), (2, 'Ferreira', 2500), (2, 'Nogare', 11999),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(3, 'Laerte', 5000), (3, 'Luciano', 23500), (3, 'Zavaschi', 13999)\r\nGO<\/pre>\n
This is what the data looks like:<\/p>\n
\"1378-image014.png\"<\/p>\n
To write this query I could do something like this:<\/p>\n
SELECT Departamentos.Nome_Dep,\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Funcionarios.Nome AS Funcionario,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Funcionarios.Salario,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AVG(Funcionarios.Salario) OVER(PARTITION BY Departamentos.Nome_Dep) \"M\u00e9dia por Departamento\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Salario - AVG(Funcionarios.Salario) OVER(PARTITION BY Departamentos.Nome_Dep) \"Diferen\u00c3\u00a7a de Sal\u00c3\u00a1rio\"\u00a0\u00a0FROM Funcionarios\r\nINNER JOIN Departamentos\r\n\u00a0\u00a0\u00a0\u00a0ON Funcionarios.ID_Dep = Departamentos.ID\r\nORDER BY 5 DESC<\/pre>\n
\"1378-image015.png\"<\/p>\n
As we can see, the employees Luciano, Nogare and Diego are receiving much more than the average of their department.<\/p>\n
Using the OVER clause, I was able to partition the data per each department, and then I could access the average with the details of the salary.<\/p>\n
You can avoid using window functions by using a query like this.<\/p>\n
SELECT Departamentos.Nome_Dep,\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Funcionarios.Nome AS Funcionario,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Funcionarios.Salario,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [M\u00e9dia por Departamento],\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Salario - [M\u00e9dia por Departamento] AS [Diferen\u00c3\u00a7a de Sal\u00c3\u00a1rio]\r\nFROM Funcionarios\r\nINNER JOIN Departamentos\r\n\u00a0\u00a0\u00a0\u00a0ON Funcionarios.ID_Dep = Departamentos.ID\r\nINNER JOIN (\r\nSELECT ID_Dep, AVG(Funcionarios.Salario) AS [M\u00e9dia por Departamento] \r\nFROM Funcionarios\r\nGROUP BY ID_Dep)[M\u00e9dia]\r\nON [M\u00e9dia].ID_Dep=Funcionarios.ID_Dep\r\nORDER BY [Diferen\u00c3\u00a7a de Sal\u00c3\u00a1rio] DESC<\/pre>\n
Example 2<\/h3>\n
Another very common problem is the “running totals”. To show this sample I’ll use a very simple data. Let’s started by creating a table called tblLancamentos where I’ve a column with a date and a column with a numeric value.<\/p>\n
IF OBJECT_ID('tblLancamentos') IS NOT NULL\r\n\u00a0\u00a0DROP TABLE tblLancamentos\r\nGO\r\n\r\n-- Tabela de Lan\u00c3\u00a7amentos para exemplificar o Subtotal\r\nCREATE TABLE tblLancamentos (DataLancamento\u00a0\u00a0Date,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValorLancamento FLOAT)\r\nGO\r\n\r\n-- Insere os registros\r\nINSERT INTO tblLancamentos VALUES ('20080623',100)\r\nINSERT INTO tblLancamentos VALUES ('20080624',-250)\r\nINSERT INTO tblLancamentos VALUES ('20080625',380)\r\nINSERT INTO tblLancamentos VALUES ('20080626',200)\r\nINSERT INTO tblLancamentos VALUES ('20080627',-300)\r\nGO<\/pre>\n
One of the alternatives to return the running total of the column ValorLancamento ordered by DataLancamento is to write a query like the following:<\/p>\n
SELECT DataLancamento, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValorLancamento,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(SELECT SUM(ValorLancamento) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM tblLancamentos\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE DataLancamento <= QE.DataLancamento) AS Saldo\r\nFROM tblLancamentos AS QE<\/pre>\n
\"1378-image016.png\"<\/p>\n
In the query above we join the table with the aggregation to return the total. Another easier, quicker and more elegant alternative would be:<\/p>\n
SELECT DataLancamento, \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ValorLancamento,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SUM(ValorLancamento) OVER(ORDER BY DataLancamento) AS Saldo\r\n\u00a0\u00a0FROM tblLancamentos AS QE<\/pre>\n
\"1378-image017.png\"<\/p>\n
In the query above I’m using the clause OVER() with an ORDER BY. Unfortunately this will only possible with next version of SQL Server, SQL Server 2012.<\/p>\n
Conclusion<\/h2>\n
In the next article I’ll show in more details the limitations of the window functions on SQL Server 2008 R2, and compare the performance of a running aggregation on SQL Server 2012 (Denali).<\/p>\n
In the final and third article I’ll show in details how the window frame works. Also I’ll show how the windowing functions LEAD, LAG, FIRST_VALUE, LAST_VALUE, PERCENT_RANK and CUME_DIST works in SQL Server.<\/p>\n
That’s all folks, see you soon with the second part of this article.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
When SQL Server introduced Window Functions in SQL Server 2005, it was done in a rather tentative way, with only a handful of functions being introduced. This was frustrating, as they remove the last excuse for cursor-based operations by providing aggregations over  a partition of the result set, and imposing an ordered sequence over a partition. Now, with SQL Server 2012, we are soon to enjoy a full range of Window Functions. They are going to make for some much simpler SQL queries.…<\/p>\n","protected":false},"author":65554,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4149,5495,4150,4754,4151,5494],"coauthors":[6809],"class_list":["post-1225","post","type-post","status-publish","format-standard","hentry","category-learn","tag-learn-sql-server","tag-sets","tag-sql","tag-sql-home","tag-sql-server","tag-windows-functions"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1225","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\/65554"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1225"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1225\/revisions"}],"predecessor-version":[{"id":77089,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1225\/revisions\/77089"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1225"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1225"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1225"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1225"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}