Dense Ranking Example

View All Scripts Login to Run Script

Script Name Dense Ranking Example
Description Illustrates how to use dense_rank to prioritize certain column values.
Area SQL Analytics
Contributor NepoBot
Created Tuesday January 09, 2018

Statement 1

Create base table

create table bunker_calc_line( 
  voy number, 
  ship_code number, 
  grade varchar2(8), 
  bunk_line_type  varchar2(4), 
  calc_status number 
)

Table created.

Statement 2

insert into bunker_calc_line values (201717,427,'LS-380','80',0)

1 row(s) inserted.

Statement 3

insert into bunker_calc_line values (201717,427,'LS-380','80',2)

1 row(s) inserted.

Statement 4

show current dataset

select * from bunker_calc_line


VOY SHIP_CODE GRADE BUNK_LINE_TYPE CALC_STATUS
201717 427 LS-380 80 0
201717 427 LS-380 80 2

2 rows selected.

VOY	SHIP_CODE	GRADE	BUNK_LINE_TYPE	CALC_STATUS
201717	427	LS-380	80	0
201717	427	LS-380	80	2

Statement 5

rank the data set use the PARTITION BY clause to specify the distinct ranking groups distinct sets of these columns will get their own ranking

select bunk.*, dense_rank() over (partition by voy, ship_code, grade, bunk_line_type order by calc_status) ranking 
from bunker_calc_line bunk 
where (bunk.VOY = 201717 and bunk.SHIP_CODE = 427 or bunk.VOY = 201801 and bunk.SHIP_CODE = 351) 
and bunk.GRADE = 'LS-380' and bunk.BUNK_LINE_TYPE = '80' 
order by bunk.VOY desc, bunk.SHIP_CODE asc


VOY SHIP_CODE GRADE BUNK_LINE_TYPE CALC_STATUS RANKING
201717 427 LS-380 80 2 2
201717 427 LS-380 80 0 1

2 rows selected.

VOY	SHIP_CODE	GRADE	BUNK_LINE_TYPE	CALC_STATUS	RANKING
201717	427	LS-380	80	2	2
201717	427	LS-380	80	0	1

Statement 6

wrap the sql with an outer query and only select the highest ranked records

select voy, ship_code, grade, bunk_line_type, calc_status from ( 
    select bunk.*, dense_rank() over (order by calc_status) ranking 
    from bunker_calc_line bunk 
    where (bunk.VOY = 201717 and bunk.SHIP_CODE = 427 or bunk.VOY = 201801 and bunk.SHIP_CODE = 351) 
    and bunk.GRADE = 'LS-380' and bunk.BUNK_LINE_TYPE = '80' 
) 
where ranking = 1 
order by VOY desc, SHIP_CODE asc


VOY SHIP_CODE GRADE BUNK_LINE_TYPE CALC_STATUS
201717 427 LS-380 80 0

VOY	SHIP_CODE	GRADE	BUNK_LINE_TYPE	CALC_STATUS
201717	427	LS-380	80	0

Statement 7

add a different grade record without a calc_status of 0

insert into bunker_calc_line values (201717,427,'LS-390','80',2)

1 row(s) inserted.

Statement 8

if a 0 calc_status is not present then the next highest calc status is returned

select voy, ship_code, grade, bunk_line_type, calc_status from ( 
    select bunk.*, dense_rank() over (partition by voy, ship_code, grade, bunk_line_type order by calc_status) ranking 
    from bunker_calc_line bunk 
    where (bunk.VOY = 201717 and bunk.SHIP_CODE = 427 or bunk.VOY = 201801 and bunk.SHIP_CODE = 351) 
    and bunk.GRADE = 'LS-390' and bunk.BUNK_LINE_TYPE = '80' 
) 
where ranking = 1 
order by VOY desc, SHIP_CODE asc


VOY SHIP_CODE GRADE BUNK_LINE_TYPE CALC_STATUS
201717 427 LS-390 80 2

VOY	SHIP_CODE	GRADE	BUNK_LINE_TYPE	CALC_STATUS
201717	427	LS-390	80	2

Statement 9

if we remove the grade filtering, it is possible for us to observe the highest ranked records across multiple distinct sets

select voy, ship_code, grade, bunk_line_type, calc_status from ( 
    select bunk.*, dense_rank() over (partition by voy, ship_code, grade, bunk_line_type order by calc_status) ranking  
    from bunker_calc_line bunk 
    where (bunk.VOY = 201717 and bunk.SHIP_CODE = 427 or bunk.VOY = 201801 and bunk.SHIP_CODE = 351) 
    and bunk.BUNK_LINE_TYPE = '80' 
) 
where ranking = 1 
order by VOY desc, SHIP_CODE asc


VOY SHIP_CODE GRADE BUNK_LINE_TYPE CALC_STATUS
201717 427 LS-390 80 2
201717 427 LS-380 80 0

2 rows selected.

VOY	SHIP_CODE	GRADE	BUNK_LINE_TYPE	CALC_STATUS
201717	427	LS-390	80	2
201717	427	LS-380	80	0

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database