If you are coming from Oracle database background, you will find it difficult in Netezza without ROWNUM pseudo column. The one possible solution to this is ROW_NUMBER() analytical function as Netezza ROWNUM pseudo column alternative. You can use ROW_NUMBER analytic function as a Netezza ROWNUM equivalent.
I think most of the distributed databases does not provide the ROWNUM columns. There is a LIMIT clause to restrict the output but very difficult to assign the sequential numbers to the rows in the Netezza tables. Even Netezza Sequences also does not produce these sequential numbers in distributed databases like Netezza, Greenplum etc.
Related reading:
ROW_NUMBER as a Netezza ROWNUM Pseudo Column Alternative
You can use the ROW_NUMBER analytical function to produce the sequential row numbers:
For Example;
TRAINING.ADMIN(ADMIN)=> SELECT ROW_NUMBER() OVER( ORDER BY MONTH_ID) as ROWNUM, MONTH_ID, SALE_QTY FROM SAMPLE_SALES; ROWNUM | MONTH_ID | SALE_QTY --------+----------+---------- 1 | 201601 | 897456 2 | 201602 | 232253 3 | 201603 | 267156 4 | 201604 | 265646 5 | 201605 | 1268957 6 | 201606 | 265654 7 | 201606 | 123457 (7 rows)
We have not provided any PARTITION BY clause here as we are partitioning the all records as single partition.
The above solution will work perfectly but issue is ROWNUM order is determined by the ORDER BY clause provide in OVER clause. The other possible best examples are:
Order the records by ROWID internal datatype:
To get sequential number as the data in the Netezza table, use the ROWID column in the ORDER BY clause of the OVER clause. For example;
TRAINING.ADMIN(ADMIN)=> SELECT ROW_NUMBER() OVER( ORDER BY ROWID) as ROWNUM, MONTH_ID, SALE_QTY FROM SAMPLE_SALES; ROWNUM | MONTH_ID | SALE_QTY --------+----------+---------- 1 | 201601 | 897456 2 | 201603 | 267156 3 | 201605 | 1268957 4 | 201602 | 232253 5 | 201604 | 265646 6 | 201606 | 265654 7 | 201606 | 123457 (7 rows)
Read: