Search This Blog

2021/02/05

POSTGRES -RANK,DENSE_RANK,ROW_NUMBER difference

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