Netezza/PureData – Row Number Function

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