what is difference between row-number,rank & dense-rank in postgres ?
Lets understand by examples.
create table for purpose of explination
CREATE TABLE emp(id serial,name varchar(100))
insert some rows
insert into emp(name) values ('sagar'),('SAGAR'),('sangram'),('sangram'),('sachin'),('swapnil')
ROW_NUMBER function:
Example:
select name, ROW_NUMBER() OVER(ORDER BY name asc) from emp;
output:
name row_number
"sachin" 1
"sagar" 2
"SAGAR" 3
"sangram" 4
"sangram" 5
"swapnil" 6
This function will sort record based on name and assign rank to it based on order.
RANK function:
Example:
select name, RANK() OVER(ORDER BY name) from emp;
Output:
name rank
sachin 1
sagar 2
SAGAR 3
sangram 4
sangram 4
swapnil 6
Here Rank of sangram is 4 for both entries as two entries for sangram next record swapnil has rank 6 instead of 5.
This function is similar to row number except identical rows get same rank & rank is skipped if identical records are found previously
Dense_Rank:
Example:
select name, DENSE_RANK() OVER(ORDER BY name) from emp
Output:
name dense_rank
"sachin" 1
"sagar" 2
"SAGAR" 3
"sangram" 4
"sangram" 4
"swapnil" 5
Here sangram occurs twice so both get same rank 4 but next record swpnil get 5th rank & rank are not skipped if identical rows found.
No comments:
Post a Comment