From: | "David Rowley" <dgrowley(at)gmail(dot)com> |
---|---|
To: | "'Hitoshi Harada'" <umi(dot)tanuki(at)gmail(dot)com> |
Cc: | <pgsql-hackers(at)postgresql(dot)org>, <heikki(dot)linnakangas(at)enterprisedb(dot)com>, "'Vladimir Sitnikov'" <sitnikov(dot)vladimir(at)gmail(dot)com> |
Subject: | Re: Windowing Function Patch Review -> Standard Conformance |
Date: | 2008-11-09 11:30:17 |
Message-ID: | E1304A0E35F34CB080A83F60AD9B71EF@amd64 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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;
Testing with:
create table employees (
id INT primary key,
name varchar(30) not null,
department varchar(30) not null,
salary int not null,
check (salary >= 0)
);
insert into employees values(1,'Jeff','IT',10000);
insert into employees values(2,'Sam','IT',12000);
insert into employees values(3,'Richard','Manager',30000);
insert into employees values(4,'Ian','Manager',20000);
insert into employees values(5,'John','IT',60000);
insert into employees values(6,'Matthew','Director',60000);
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2008-11-09 12:32:02 | Windowing Function Patch Review -> NTILE function |
Previous Message | David Rowley | 2008-11-09 11:08:15 | Re: Windowing Function Patch Review -> Standard Conformance |