What is DENSE_RANK analytic function? When it can be used?

Let's assume we want to assign a sequential order, or rank, to the payments made by customers based on paid date, we might use the DENSE_RANK function like:


with t
 as
 (
 select 1 id, 'A' name, to_date('05-Sep-11','dd-mon-rr') dt, 10 paid from dual union all
 select 1, 'A', to_date('06-Sep-11','dd-mon-rr'), 12 from dual union all
 select 1, 'A', to_date('07-Sep-11','dd-mon-rr'), 15 from dual union all
 select 1, 'A', to_date('07-Sep-11','dd-mon-rr'), 13 from dual union all
 select 2, 'B', to_date('06-Sep-11','dd-mon-rr'), 13 from dual union all
 select 2, 'B', to_date('07-Sep-11','dd-mon-rr'), 16 from dual union all
 select 2, 'B', to_date('08-Sep-11','dd-mon-rr'), 05  from dual union all
 select 3, 'C', to_date('08-Sep-11','dd-mon-rr'), 12 from dual union all
 select 3, 'C', to_date('09-Sep-11','dd-mon-rr'), 11 from dual
 )
 select id, name, max(paid) keep(dense_rank last order by dt)  last_paid
   from t
   group by id,name


ID     NAME LAST_PAID                   
-------- ---- ---------------------- 
1       A      15                     
2       B      5                      
3       C      11

Dense_Rank should be used along with FIRST or LAST functions. The KEEP keyword is for semantic clarity. It qualifies aggregate_function, indicating that only the FIRST or LAST values of aggregate_function will be returned.

Logic:
  1) Rows are grouped by ID and Name.
  2) Sorts the rows within the group. 
  3) Dense_Rank ranks the rows within the group with respect to sorted
order,say it ranks first row as "1". When there is two same dates within
the same group then Dense_Rank ranks them with the same number.
  4) Then it returns the maximum paid from the group.When there is no two rows with the same ranks within the group max/min works similar. When there is two rows with same rank then max/min fetches the corresponding value.
      
Other example: Select salary which is earned by the most employees+how often
                
with testdata as (
select 1000 as salary from dual
union all
select 1000 as salary from dual
union all
select 1000 as salary from dual
union all
select 20000 as salary from dual
union all
select 2000 as salary from dual
union all
select 10 as salary from dual
)


CNT                    SALARY                 
---------------------- ---------------------- 
3                      1000                   

Query 1

select max(count_emps) as Cnt, max(salary) keep (dense_rank first order by count_emps desc) as Salary
from (SELECT salary, COUNT(*) as count_emps
        FROM testdata
       group by salary )

Query 2

select max(COUNT(*)) cnt, max(salary) keep (dense_rank first order by COUNT(*) desc) salary
FROM testdata
group by salary;