Netezza / PureData – Row number within a group

Netezza / PureData – row number within a group

SQL (Structured Query Language)

Sometimes it is useful to be able number data with it groups, which can be quickly and easily done suing the Row Number function in Netezza. However, you do need to pay particular attention to you ‘partition by’ (conceptually a group by operation) field list and field order, as well as, you ‘Order by’ field list.  Otherwise, you can get some bizarre  results.  it find it helpful to think of them as having a parent, child, relationship.  In this line of thought, your ‘partition by’ field list should usually have one less more field than than your ‘order by’.  This is not an absolute rule, but under normal circumstances your  ‘order by’ field list should contain all the field in your ‘partition by’  and, occasionally, provide a orby direction (ascending or descending).

SQL Netezza Row Number Format

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

 

Example Row Number SQL

Row Number Within A Group

Row Number Within A Group

SELECT

qh_database

,qh_sessionid

, qh_tend

,ROW_NUMBER() OVER( PARTITION BY qh_database, qh_sessionid

ORDER BY qh_database, qh_sessionid, qh_tend desc ) as ROW_NUM

FROM nz_query_history

where qh_database = ‘system’

order by qh_database, qh_sessionid, qh_tend desc

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

 

 

 

 

 

 

2 thoughts on “Netezza / PureData – Row number within a group

  1. Pingback: Netezza/PureData – Row Number Function | Scientia Compendium

  2. Pingback: Netezza / PureData – how to get the first row_number by group | Scientia Compendium

Comments are closed.