Reference > Prepare Methods

rank(value)

Definition

Integer rank(Comparable value)

Description

will evaluate value for all rows and the lowest value will get 1, 2nd will get 2, etc. If multiple rows value evaluate to the same value, they will get the same ranking.

Parameter Definition

Name	Type	Description
value	Comparable

Example 1

CREATE TABLE input(symbol String, id int, price double);
INSERT INTO input VALUES ("CAT", 1, 50),("CAT", 2, 54),("CAT", 3, 52),("CAT", 4, 52),("CAT", 5, null),("CAT", 6, 57);
INSERT INTO input VALUES ("IBM", 1, 20),("IBM", 2, 24),("IBM", 3, 22),("IBM", 4, 27),("IBM", 5, null);
CREATE TABLE result AS PREPARE *,rank(price) AS newCol FROM input PARTITION BY symbol;
Table input = SELECT * FROM input;
TABLE result = SELECT * FROM result;

// input = 
// +---------------------+
// |        input        |
// +------+-------+------+
// |symbol|id     |price |
// |String|Integer|Double|
// +------+-------+------+
// |CAT   |1      |50.0  |
// |CAT   |2      |54.0  |
// |CAT   |3      |52.0  |
// |CAT   |4      |52.0  |
// |CAT   |5      !null  |
// |CAT   |6      |57.0  |
// |IBM   |1      |20.0  |
// |IBM   |2      |24.0  |
// |IBM   |3      |22.0  |
// |IBM   |4      |27.0  |
// |IBM   |5      !null  |
// +------+-------+------+
// 
// result = 
// +-----------------------------+
// |           result            |
// +------+-------+------+-------+
// |symbol|id     |price |newCol |
// |String|Integer|Double|Integer|
// +------+-------+------+-------+
// |CAT   |1      |50.0  |1      |
// |CAT   |2      |54.0  |3      |
// |CAT   |3      |52.0  |2      |
// |CAT   |4      |52.0  |2      |
// |CAT   |5      !null  !null   |
// |CAT   |6      |57.0  |4      |
// |IBM   |1      |20.0  |1      |
// |IBM   |2      |24.0  |3      |
// |IBM   |3      |22.0  |2      |
// |IBM   |4      |27.0  |4      |
// |IBM   |5      !null  !null   |
// +------+-------+------+-------+
//