Prev: query plan for stored procedure
Next: I assume Row_Number doesn’t act only onrows of the window frame
From: trubar a on 30 Apr 2010 15:35
Quotes are taken from http://www.postgresql.org/docs/current/static/tutorial-window.html

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?

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 )?

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?

thanx
From: trubar a on 1 May 2010 14:34
On Apr 30, 2:53 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote:
> trubar a (asponmy...(a)gmail.com) writes:
>
> > 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.
>

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?

> > 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.
>

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

thanx

From: trubar a on 3 May 2010 15:03
thank you for your help

 | 
Pages: 1
Prev: query plan for stored procedure
Next: I assume Row_Number doesn’t act only onrows of the window frame