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 back ground where ROWNUM pseudocolumn 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.
ROW_NUMBER() OVER( PARTITION BY NULL ORDER BY <<Sort_By_Field_List [direction asc/desc]>> ) as ROW_NUM