Re: SQL Partition Clause

amerar_at_iwc.net wrote:
> Hey All,
>
> I'm trying to understand the 'partition by' clause. I'm reading a
> bunch of webpages and they are showing examples and all. But when you
> come across say a function like SUM, you can easily form a mental
> picture of the rows being summed......
>
> I'm trying to really see what 'parition by' does. I am not grasping
> how the function operates on the data......
>
> If someone can provide a 'dummy' explanation, it might help.
Hmm I will try.
PARTITION BY == GROUP BY minus aggregation

CREATE TABLE T(x INT, y INT);
SELECT x, SUM(y) FROM T GROUP BY x
=> returns one row per group with the y's summed up.

SELECT x, y, SUM(y) OVER(PARTITION BY x) FROM T => returns all rows in T, but each row contains the sum for the whole group.

It's equivalent to:
SELECT x, y, (SELECT SUM(y) FROM T AS S WHERE S.x = T.x) FROM T

Hope that helped
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab