ANALYTICAL FUNCTION
RANK()
DENSE RANK()
ROW_NUMBER()
LEAD()
LAG()
ROLLUP()
CUBE()
FIRST()
LAST()
FIRST_VALUE()
SQL> CREATE Table t1(Id Number, Name Varchar2(10), Salary Number);
Table created.
SQL> INSERT into t1 values(1,'Parthi',24000);
SQL> INSERT into t1 values(2,'Karthi',20000);
SQL> INSERT into t1 values(3,'Arthi',20000);
SQL> INSERT into t1 values(4,'Surya',9000);
SQL> INSERT into t1 values(5,'Taj',8000);
SQL> INSERT into t1 values(6,'Priya',19000);
SQL> SELECT * FROM t1;
ID NAME SALARY
---------- ---------- ----------
1 Parthi 24000
2 Karthi 20000
3 Arthi 20000
4 Surya 9000
5 Taj 8000
6 Priya 19000
In the ANALYTICAL Function there are two keywords used.
OVER (ORDER BY COLUMN)
OVER (PARTITION BY COLUMN ORDER BY COLUMN)
RANK() : Equal rows are ranked the same rank.Next rank will be empty.
SQL> SELECT Id, Name, Salary, RANK() over (order by salary desc) "RANK" from t1;
ID NAME SALARY RANK
---------- ---------- ---------- ------------
1 Parthi 24000 1
2 Karthi 20000 2
3 Arthi 20000 2
6 Priya 19000 4
4 Surya 9000 5
5 Taj 8000 6
6 rows selected.
N th MAXIMUM Salary using RANK()
SELECT First_Name,Salary FROM
( SELECT First_Name,Salary, RANK() OVER (ORDER BY Salary DESC) "RANK" from Employees ) where RANK = 5;
N th MAXIMUM Salary department wise by using RANK()
SELECT First_Name,department_id,Salary FROM
( SELECT First_Name,Salary,department_id, RANK() OVER (partition by department_id ORDER BY Salary DESC) "RANK" from Employees ) where RANK = 2;
DENSE_RANK() : Equal rows are ranked the same rank.Next rank will be named as the next position
SQL> SELECT Id, Name, Salary,DENSE_RANK() over (order by salary desc) from t1;
ID NAME SALARY DENSE_RANK()OVER(ORDERBYSALARYDESC)
---------- ---------- ---------- -----------------------------------
1 Parthi 24000 1
2 Karthi 20000 2
3 Arthi 20000 2
6 Priya 19000 3
4 Surya 9000 4
5 Taj 8000 5
6 rows selected.
Nth MAXIMUM Salary by Using DENSE_RANK
SELECT First_Name,Salary FROM
( SELECT First_Name,Salary,DENSE_RANK() OVER (ORDER BY Salary DESC) "RANK" from Employees ) where RANK = 5;
N th MAXIMUM Salary department wise by using RANK()
SELECT First_Name,department_id,Salary FROM
( SELECT First_Name,Salary,department_id, DENSE_RANK() OVER (partition by department_id ORDER BY Salary DESC) "RANK" from Employees ) where RANK = 2;
ROWNUM
SQL> SELECT Id, Name, Salary,ROW_NUMBER() over (order by salary desc) from t1;
ID NAME SALARY ROW_NUMBER()OVER(ORDERBYSALARYDESC)
---------- ---------- ---------- -----------------------------------
1 Parthi 24000 1
2 Karthi 20000 2
3 Arthi 20000 3
6 Priya 19000 4
4 Surya 9000 5
5 Taj 8000 6
6 rows selected.
N th MAXIMUM Salary By Using ROWNUM
SELECT * FROM
(
SELECT Rownum rn, Salary FROM
(
SELECT DISTINCT Salary FROM Employees Order By Salary DESC)
)
WHERE rn=&n;
SELECT MIN(Salary) FROM
(SELECT ROWNUM Rn, Salary FROM
(
SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC)
)
WHERE ROWNUM<=5;
SELECT First_name, department_id, salary from
(
SELECT First_name, department_id, salary, row_number() OVER (ORDER BY salary DESC) as row_num from Employees e
)
where row_num = 2;
N th MAXIMUM Salary of department wise By Using ROWNUM
SELECT First_name, department_id, salary from
(
SELECT First_name, department_id, salary, row_number() OVER (PARTITION BY department_id ORDER BY salary DESC) as row_num from Employees e
)
where row_num = 2;
LEAD
SQL> SELECT Id, Name, Salary,LEAD(Salary,1,0) over (order by salary desc) from t1;
SQL> SELECT Id, Name, Salary,LEAD(Salary,2,100) over (order by salary desc) from t1;
LAG
SQL> SELECT Id, Name, Salary,LAG(Salary,2,100) over (order by salary desc) from t1;
6 rows selected.
SQL> SELECT Id, Name, Salary,LAG(Salary,1,0) over (order by salary desc) from t1;
6 rows selected.
FIRST_VALUE
SELECT employee_id, department_id, hire_date , FIRST_VALUE(hire_date)
OVER (PARTITION BY department_id ORDER BY hire_date) DAY_GAP, hire_date- (FIRST_VALUE(hire_date)
OVER ( partition by department_id ORDER BY hire_date)"Day Count"
FROM employees
WHERE department_id IN (20, 30)
ORDER BY department_id, DAY_GAP;
EMPLOYEE_ID DEPARTMENT_ID HIRE_DATE DAY_GAP Day Count
----------- ------------- --------- --------- ----------
201 20 17-FEB-04 17-FEB-04 0
202 20 17-AUG-05 17-FEB-04 547
117 30 24-JUL-05 07-DEC-02 960
116 30 24-DEC-05 07-DEC-02 1113
118 30 15-NOV-06 07-DEC-02 1439
119 30 10-AUG-07 07-DEC-02 1707
114 30 07-DEC-02 07-DEC-02 0
115 30 18-MAY-03 07-DEC-02 162
FIRST AND LAST
SELECT department_id,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
FROM employees
GROUP BY department_id
ORDER BY department_id;
DEPARTMENT_ID Worst Best
------------- ---------- ----------
10 4400 4400
20 6000 13000
30 2500 11000
40 6500 6500
50 2100 8200
60 4200 9000
70 10000 10000
80 6100 14000
90 17000 24000
100 6900 12008
110 8300 12008
7000 7000
LISTAGG()
DEPT_ID DNAME
---------- ----------
10 cse
10 ece
20 eee
30 mech
10 mech
20 it
30 it
SELECT Dept_Id, LISTAGG(DName,',')within group (order by dname)"List" FROM Listagg1 group by dept_id;
DEPT_ID DNAME
---------- ----------
10 cse,ece,mech
20 eee,it
30 mech,it
ROLLUP
SQL> SELECT * FROM t3;
DEP_ID SID SALARY
---------- ---------- ----------
1 1 4000
1 2 6000
1 3 6000
1 4 6500
1 5 7500
2 1 7500
2 2 6500
2 3 8500
2 4 9500
2 5 9000
10 rows selected.
SELECT dep_id,sid,SUM(salary)
FROM t3 GROUP BY ROLLUP(dep_id,sid) ORDER BY dep_id,sid;
DEP_ID SID SUM(SALARY)
---------- ---------- -----------
1 1 4000
1 2 6000
1 3 6000
1 4 6500
1 5 7500
1 30000
2 1 7500
2 2 6500
2 3 8500
2 4 9500
2 5 9000
2 41000
71000
CUBE
SQL> Select dep_id,sid,sum(salary) from t3 group by CUBE(dep_id,sid) order by dep_id,sid;
DEP_ID SID SUM(SALARY)
---------- ---------- -----------
1 1 4000
1 2 6000
1 3 6000
1 4 6500
1 5 7500
1 30000
2 1 7500
2 2 6500
2 3 8500
2 4 9500
2 5 9000
2 41000
1 11500
2 12500
3 14500
4 16000
5 16500
DEP_ID SID SUM(SALARY)
---------- ---------- -----------
71000
