2008/11/9 David Rowley <dgrowley@gmail.com>:
> Using one of my original test tables I'm testing windowing functions with a
> GROUP BY.
>
> The following query works as I would expect.
>
> -- Works
> SELECT department,
> SUM(Salary),
> ROW_NUMBER() OVER (ORDER BY department),
> SUM(SUM(salary)) OVER (ORDER BY department)
> FROM employees
> GROUP BY department;
>
>
> The following one fails with the message.
> ERROR: variable not found in subplan target list
>
> -- Does not work.
> SELECT department,
> SUM(Salary),
> ROW_NUMBER() OVER (ORDER BY department),
> SUM(SUM(salary)) OVER (ORDER BY department DESC)
> FROM employees
> GROUP BY department;
>
> I just added the DESC to force it into creating 2 separate windows.
>
> I can re-write the non working query to work using the following:
>
>
> SELECT department,
> salary,
> ROW_NUMBER() OVER (ORDER BY department),
> SUM(salary) OVER (ORDER BY department DESC)
> FROM (SELECT department,
> SUM(salary) AS salary
> FROM employees
> GROUP BY department
> ) t;
>
>
Thank you for your tests again. Attached are both of delta from the
previous and the whole version agains today's HEAD. Note that HEAD has
changed pg_class.h, which conflicted with my local repository so if
you try it update your local with HEAD and apply the patch. Though,
the delta one seem to be applicable without sync.
Regards,
--
Hitoshi Harada