rank vs dense rank in Oracle SQL

  • Rank – If two values are exactly same then both the person is assigned same rank but the next rank is skipped
  • Dense rank – If two values are exactly same then both the person is assigned same rank but the next rank is not skipped.

Example

select rank() over(order by AVAIL_BALANCE desc),
dense_rank() over(order by AVAIL_BALANCE desc),
ACCOUNT_ID, AVAIL_BALANCE, CLOSE_DATE, LAST_ACTIVITY_DATE, OPEN_DATE, PENDING_BALANCE, STATUS, CUST_ID, OPEN_BRANCH_ID, OPEN_EMP_ID, PRODUCT_CD 
from ACCOUNT;

Leave a Comment