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
3 thoughts on “Netezza/PureData – Row Number Function”