The rank() will return a 1-based index for each record in an ordered partition in Standard SQL. Remember that the function will assign the same rank value to the partitions with similar values.
The number of previous rank values increments each consequent row rank value. If you wish to increment the rank value by 1, use the dense_rank() function.
Rank() Function Syntax
The syntax for the rank function is as shown below:
[PARTITION BY expression, ]
ORDER BY expression (ASC | DESC) );
);
To illustrate how to use this function, consider the example below:
id serial primary key,
first_name varchar(100),
last_name varchar(100),
state varchar(25),
active bool
);
insert into users(first_name, last_name, state, active) values (
'Mary', 'Smith', 'New York', TRUE);
insert into users(first_name, last_name, state, active) values (
'Linda', 'Williams', 'Colorado', FALSE);
insert into users(first_name, last_name, state, active) values (
'Taylor', 'Moore', 'Utah', TRUE);
insert into users(first_name, last_name, state, active) values (
'Susan', 'Wilson', 'Washington', TRUE);
insert into users(first_name, last_name, state, active) values (
'Mary', 'Smith', 'New York', TRUE);
insert into users(first_name, last_name, state, active) values (
'Taylor', 'Moore', 'Utah', TRUE);
The above queries create and insert sample data into the table.
To assign a rank to the records in the result set, we can use the rank() function as illustrated below.
rank () over (partition by active order by id) rank_value
from users;
In the above query, we partition the data by the active column. The column contains Boolean values. We then rank over each item in the partitions.
The resulting set is as shown:
Note that the result contains two partitions, one containing false values and the other containing true values.
In the “false’ partition, the function assigns the rank values. The same case for the “true” partition. Note that the function starts the rank value from 1 in a new partition.
Closing
This tutorial showed you how to perform row ranking by partitioning using the rank() function. Check the documentation for your database engine to learn more.
Thanks for reading!!
