Ranking family syntax

The syntax for the ranking family is as follows:

Function ( ) OVER (
      ([partition by <value_expression>    [,...]]
      ORDER BY <value_expression> [asc|desc] [nulls {first | last}] 
[,...]))

rank

Calculates the rank of a value in a group of values. Rows with equal values for the ranking criteria receive the same rank. If there are ties, the number of tied rows is added to the tied rank to calculate the next rank. For example, if three people tie for second place, all three would be in second place and the next person would be in fifth place.

dense_rank

Returns the rank of a row in an ordered group of rows. The ranks are consecutive integers that begin with one. The largest rank value is the number of unique values the query returns. If there are ties, Netezza Performance Server SQL does not skip rank values, but rather assigns rows with equal values the same rank. For example, if three people tie for second place all three would be in second place and the next person would be in third place.

percent_rank

This is zero, if the number of rows in the partition is 1. Otherwise, it is the (rank of row R-1), divided by the number of rows in the partition of row R-1.

cume_dist

This is the number of rows preceding, or is peer with current row R, divided by the number of rows in the partition of current row R.

ntile (expr)

Divides an ordered set of rows into a number of bins, which are numbered 1 to <expr>. The count of rows in all the bins differs at most by one. This function returns the bin number that is assigned to the current row. The argument (expr) evaluates to a numeric constant. If it is not an integral constant, it is rounded to an integer.