KEEP DENSE_RANK is great functionality within the Oracle database; here is why.

Suppose you have a table with a date and a status field:

table mytable
( date_created date
, status varchar2 )

Status can have 2 values: A or B

If you want to fetch only 1 row based on the most recent date_created you would do something like this:

select *
from mytable
where date_created = (select max(date_created) from mytable);

No problem so far... 
But, suppose you want to have the row with the most recent date_created for status A, if it exists and if not, you just want to have the most recent date_created.
In the old times your solution would be something like this:

select *
from mytable
where date_created = (select max(date_created) from mytable where status = 'A')
or 
not exists ( select 1 from mytable where status = 'A' )
and date_created = (select max(date_created) from mytable);

Lots of code and what about performance ...

From now on, you can use keep dense_rank for this kind of "problems":

select *
from mytable
where date_created = (select max(date_created) KEEP (DENSE_RANK FIRST ORDER BY status) 
from mytable);

 

Joomla templates by a4joomla