Prev: I assume Row_Number doesn’t act only on rows of the window frame
Next: Ending a stored proc
From: Erland Sommarskog on 30 Apr 2010 17:53
trubar a (asponmynet(a)gmail.com) writes:
> a)
> "for each row, there is a set of rows within its partition called its
> window frame. Many (but not all) window functions act only on the rows
> of the window frame, rather than of the whole partition. By default,
> if ORDER BY is supplied then the frame consists of all rows from the
> start of the partition up through the current row, plus any following
> rows that are equal to the current row according to the ORDER BY
> clause"
>
> I assume Row_Number doesn't act only on rows of the window frame, but
> instead always act on all rows of a partition?

I would say that row_number() only acts on the current row, but that
is a matter of definition. The ORDER BY clause is mandatory for
row_number, so you cannot have it to "act on the whole partition". One
could also say that the distinction is meaningless for row_number.

> b)
> "By default, if ORDER BY is supplied then the frame consists of all
> rows from the start of the partition up through the current row, plus
> any following rows that are equal to the current row according to the
> ORDER BY clause"
>
> I assume that is only true for those window functions that act only on
> rows of the window frame ( thus above quote isn't true for
> ROW_NUMBER() function )?

I guess the whole point of introducing the window frame is for windowing
functions to describe what happens with windowing functions where
ORDER BY is optional.

> c) "http://www.postgresql.org/docs/current/static/tutorial-
> window.html" article talks about PostgreSQL 8.4's Windowing functions.
> Is everything in that article also true for Sql Server 2008's
> Windowing functions?

Unfortunately no. This example:

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

Results in:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.

That is, ORDER BY for SUM() and other functions is not supported in
SQL Server. It would be very very useful it was. Likewise:

SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

The WINDOW clause is not supported in SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Erland Sommarskog on 1 May 2010 17:48
trubar a (asponmynet(a)gmail.com) writes:
> I only covered ROW_NUMBER, but I assume with NTILE and RANK functions
> the ORDER BY clause is not optional and thus the two functions also
> don�t act on rows of a window frame?

Yes.

> But other than what you've mentioned above, everything else in that
> article is also true for MS SQL server 2008?

I think so.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: I assume Row_Number doesn’t act only on rows of the window frame
Next: Ending a stored proc