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