• 

I recently had to research how to create a unique row number for a select result in a Netezza query (1 to Last row number).  So, I thought would share the things I learned, having come from an Oracle background where ROWNUM pseudo column does this very nicely.

My solution, which worked

• Using the ROW_NUMBER() function
• Using a ‘Partition By’ on ‘NULL’
• Creating a composite unified sort key. Using multiple fields would reset the count on the last field break.  The derived composite sort key field got past that problem.

SQL Format:

ROW_NUMBER() OVER( PARTITION BY NULL ORDER BY <<Sort_By_Field_List [direction asc/desc]>> ) as ROW_NUM

Related References

• IBM® PureData System for Analytics, Version 7.2.1 – Functions
• Netezza / PureData – how to get the first row_number by group
• Netezza / PureData –row number within a group