Does the Netezza SQL dialect support a window function in the HAVING clause (or has it ever)? For example: SELECT *, RANK() OVER (ORDER BY amt DESC) AS ranking FROM sales_tbl HAVING ranking <= 2; My understanding is that this query must be of the form: SELECT * FROM ( SELECT *, RANK() OVER (ORDER BY amt DESC) AS ranking FROM sales_tbl ) AS subsel WHERE ranking <= 2; Thanks in advance.

Got the error message on 4.6.5: Error: ERROR: HAVING clause cannot contain windowed functions.

I had to use the windowed function as a subselect in Netezza whenever I was using the result set from windowed function to filter/ manipulate data.

Can you confirm this? If so, can you please state the version number you used?

It matters because I’m trying to understand if Netezza supports this as it is not ANSI standard (cant find any other database that does support this).

In at least one version of Netezza (not sure the version) it returns:
ERROR: HAVING clause cannot contain windowed functions

either of these would work. Why does it matter?

From: GregRahn via netezza-l [ mailto:netezza-l@Groups.ITtoolbox.com ]
Sent: Monday, January 11, 2010 11:25 AM
To: Remediator
Subject: [netezza-l] Using window functions in HAVING clause

Image removed by sender.

Posted by GregRahn (Performance
Engineer)
on Jan 11 at 12:25 PM

Does the Netezza SQL dialect support a window function in the HAVING clause
(or has it ever)?
For example:
SELECT *, RANK() OVER (ORDER BY amt DESC) AS ranking
FROM sales_tbl
HAVING ranking <= 2;
My understanding is that this query must be of the form:
SELECT * FROM (
SELECT *, RANK() OVER (ORDER BY amt DESC) AS ranking
FROM sales_tbl ) AS subsel
WHERE ranking <= 2;
Thanks in advance.