Rownum In Netezza?

Those of you coming from the Oracle world may find you are missing a ROWNUM pseudo column in Netezza.  Sure there’s the LIMIT clause, but sometimes you have a need to assign a sequential number to each row.  The best solution I’ve found is using the analytic function ROW_NUMBER. 

Examples:

SELECT ROW_NUMBER() OVER (ORDER BY CUSTOMER_ID_PK),
*
FROM CUSTOMER
ORDER BY 1;

Important to realize, unlike the ROWNUM the ordering is determined by the ORDER defined in the OVER clause, not by the ordering at the end of the select. I only added the ordering at then end so you see the results sequential, not in the somewhat random spu returned order.

Other examples;

SELECT ROW_NUMBER() OVER (ORDER BY NULL),
*
FROM CUSTOMER
ORDER BY 1;

SELECT ROW_NUMBER() OVER (ORDER BY ROWID),
*
FROM CUSTOMER
ORDER BY 1;

Those familiar with analytic functions may notice the lack of a PARTITION statement.  When it is omitted, the partition is the entire record set.

This entry was posted in SQL Tricks and tagged , , , . Bookmark the permalink.

2 Responses to Rownum In Netezza?

  1. Bob Johnson says:

    The problem with ROW_NUMBER() OVER (ORDER BY ROWID) is that it forces you to sort the dataset, and is significantly slower than simply using “rowid” as the output (or rownum in oracle). For whatever reason, Netezza chose not to allow row_number() over () with an empty over clause—something that appears to work just fine in greenplum.

    • NZGuy says:

      So if you look, I also give an example ROW_NUMBER() OVER(ORDER BY NULL). This should achieve the same thing as your OVER().

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>