Advertisements

SQL (Structured Query Language)

Sometimes it is useful to be able to number data with its groups, which can be quickly and easily done using 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 finds 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 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 an order by 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

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