2008/12/6 David Rowley <[EMAIL PROTECTED]>: > > I've spent last night and tonight trying to break the patch and I've not > managed it. > > I spent 2 and a half hours on the train last night reading over the patch > mainly for my own interest. I also went over the documentation and I have a > few suggestions for improvement: > > + <para> > + After <literal>WHERE</> and <literal>GROUP BY</> process, > + rows might be windowed table, using the <literal>WINDOW</> > + clause. > + </para> > > I think I know what you mean here. My re-write seems to have turned the > sentence into a paragraph. Please tell me if I've assumed the meaning > wrongly: > > > "After the <literal>WHERE</>, <literal>GROUP BY</> and <literal>HAVING</> > clauses one or more <literal>WINDOW</> clauses can be specified. This will > allow window functions to be specified in the <literal>SELECT</> clause. > These window functions can make use of the <literal>WINDOW</> clauses by > making reference to the alias name of the window rather than explicitly > specifying the properties of the window in each <literal>OVER</> clause."
The "WINDOW clause" is a clause that starts with WINDOW, containing some window definitions, syntactically. So I rewrote it as: >> After the <literal>WHERE</>, <literal>GROUP BY</> and <literal>HAVING</> clauses one or more window definitions can be specified by the <literal>WINDOW</> clause. This will allow window functions to be specified in the <literal>SELECT</> clause. These window functions can make use of the <literal>WINDOW</> clauses by making reference to the alias name of the window rather than explicitly specifying the properties of the window in each <literal>OVER</> clause. << > > > + Window functions are not placed in any of GROUP BY, HAVING and > + WHERE clauses, which process values before any of the windows. If > + there is need to qualify rows by the result of window functions, > + whole of the query must be nested and append WHERE clause outer of > + the current query. > > I think this one maybe needs an example to back it up. It's quite an > important thing and I'm sure lots of people will need to do this. I'm not > 100% happy with my new paragraph either but can't see how to word it any > better. > > "Window functions cannot be used in the WHERE, GROUP BY or HAVING clauses > of the query. If there is a need to filter rows, group results or filter > rows after aggregation takes place (HAVING) then the query must be nested. > The query should contain the window functions in the inner query and apply > the additional clauses that contain the results from the window function in > the outer query, such as: > > SELECT depname, > empno, > salary, > enroll_date > FROM (SELECT depname, > empno, > salary, > enroll_date, > ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary,empno) > AS pos > FROM empsalary > ) AS e > WHERE pos = 1; > > In the above query the we're filtering and only showing the results from the > inner query where the ROW_NUMBER() value is equal to 1." > > But of course the above query would be more simple using DISTINCT ON. Maybe > there is a better example... My previous marathon getting the person in 2nd > place might be better but that's introducing another previously unknown > table to the manual. I use this query: SELECT depname, empno, salary, enroll_date FROM (SELECT depname, empno, salary, enroll_date, ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary,empno) AS pos FROM empsalary ) AS e WHERE pos < 3; This isn't emulated by DISTINCT ON, is it? For all other issues, thanks, applied to my patch. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers