Row Numbering and Ranking Functions

1. ROWNUM without Analytic Function

select employee_id, name , salary, ROWNUM
from employees ORDER BY salary;exec

2. ROW_NUMBER ( )

select employee_id, name, salary, ROW_NUMBER() OVER (ORDER BY salary  )toprank
FROM employees;exec

3. RANK ( )

select employee_id, name, salary, RANK() OVER (ORDER BY salary )toprank
FROM employees;exec

4. DENSE_RANK ( ) with WHERE clause

select * from
( select employee_id, name, salary, DENSE_RANK() OVER (ORDER BY salary  desc ) toprank
FROM employees )t
where t.toprank < 3;exec

5. RANK ( ) with order by

select employee_id, first_name, salary, RANK() OVER (ORDER BY salary  desc) toprank
FROM employees
order by salary;exec

6. RANK ( ) with a join

select e.employee_id, e.name, j.job_title, e.salary,
RANK() OVER (ORDER BY e.salary desc) rankorder
from employees e, jobs j
where e.salary < 3000
and e.job_id = j.job_id
order by salary;exec

7. RANK ( ) contains a join, group by and order by

select j.job_title, COUNT(*), MAX(e.salary) maxsalary,
MIN(e.salary) minsalary,
RANK() OVER (ORDER BY MAX(salary)) rankorder
FROM employees e, jobs j
where e.salary < 5000
AND e.job_id = j.job_id
GROUP BY j.job_title
ORDER BY j.job_title;exec

8. RANK and DENSE_RANK ( ); GROUP BY, HAVING and ORDER BY clause

More than one analytical function can be used in one sql.

select j.job_title,COUNT(*),MAX(salary) maxsalary,
MIN(salary) minsalary,RANK () OVER (ORDER BY MAX(salary)) rankorder,
DENSE_RANK () OVER (ORDER BY MIN(salary)) rankorder
FROM employees e, jobs j
WHERE e.salary < 6000
AND e.job_id = j.job_id
GROUP BY j.job_title
HAVING MIN(salary) > 2000
ORDER BY j.job_title desc;exec

9. ROW_NUMBER ( ) and NULLS LAST

select e.employee_id, e.name, e.salary curr_salary ,
ROW_NUMBER () OVER (ORDER BY salary desc NULLS LAST ) salary
from employees e
ORDER BY curr_salary desc NULLS LAST;exec

10. RANK ( ),  NVL function and NULLS LAST

SELECT e.employee_id, e.name, NVL (salary, 9999),
RANK () OVER (ORDER BY NVL (salary,9999) desc NULLS LAST) salary
from empnls e
where employee_id < 115
ORDER BY salary desc NULLS LAST;exec

11. RANK ( ), Order by after NLV function

SELECT employee_id,
name,
NVL(salary, 6666),
RANK() OVER (ORDER BY NVL (salary, 6666) desc) curr_salary
FROM empnls
ORDER BY curr_salary;exec

12. RANK ( ), Partitioning with PARTITION_BY

SELECT employee_id,
       name,
       department_id,
       salary,
       RANK() OVER(PARTITION BY department_id ORDER BY salary desc) rank
  FROM employees
  ORDER BY department_id;exec

13.  NTILE ( )

This function must apply same segment at the same intervals except last interval and if the remaining segment is equals or more than rownum, segment is always equals rownum.

Interval = (ROUND(rowcount /segment));

SELECT employee_id,
       name,
       salary,
       NTILE(4) OVER(ORDER BY salary desc) ntlist4,
       NTILE(8) OVER(ORDER BY salary desc) ntlist8,
       NTILE(100) OVER(ORDER BY salary desc) ntlist107,
       NTILE(120) OVER(ORDER BY salary desc) ntlist120
 FROM employees;exec

~ by enginzorlu on November 14, 2008.