Oracle

difference between row_num, rank and dense_rank

I didn't know about RANK and DENSE_RANK until I needed them for a project. Here are some of the details about these functions

ROW_NUMBER(): This one generates a new row number for every row, regardless of duplicates within a partition.
RANK(): This one generates a new row number for every distinct row, leaving gaps between groups of duplicates within a partition.
DENSE_RANK(): This one generates a new row number for every distinct row, leaving no gaps between groups of duplicates within a partition.

CREATE TABLE t AS
SELECT 'a' v FROM DUAL UNION ALL
SELECT 'a' FROM DUAL UNION ALL
SELECT 'a' FROM DUAL UNION ALL
SELECT 'b' FROM DUAL UNION ALL
SELECT 'c' FROM DUAL UNION ALL
SELECT 'c' FROM DUAL UNION ALL
SELECT 'd' FROM DUAL UNION ALL
SELECT 'e' FROM DUAL
SELECT v, 
row_number() OVER(PARTITION BY v ORDER BY v) rown,
 DENSE_RANK() OVER( ORDER BY v) drank,
RANK() OVER( ORDER BY v) rankd
 FROM t

row_num_dense_rank_rank_oracle_sql

These functions are pretty handy.

In the real world example, I needed to extract the latest entry in the asset table of Siebel CRM's Sales Order Module based on the service start date. In Siebel-SOM, a reconnection mean a new copy of asset is created and that's why there are numerous entries per mobile asset. And we only need to get the latest entry regardless of its status. So here's the query to just do that.

SELECT d.asset_num, d.start_dt, d.end_dt, d.serial_num, d.status_cd
FROM (
SELECT mob.asset_num, mob.start_dt, mob.end_dt, mob.serial_num, mob.status_cd, ROW_NUMBER() OVER (PARTITION BY mob.serial_num ORDER BY mob.start_dt DESC) ctr
FROM siebel.s_asset mob
WHERE mob.prod_id = '6SIA-4N275' AND NVL(mob.end_dt,SYSDATE) > SYSDATE - 90 AND mob.sub_type_cd = 'Asset') d WHERE d.ctr = 1

References:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions043.htm

https://blog.jooq.org/2013/10/09/sql-trick-row_number-is-to-select-what-dense_rank-is-to-select-distinct/