1) RANK
It provides a rank to record based on some column value. In case if a tie of 2 records occurs at position N then the two record positions will be N and give N+2 to the next record.
Example
select empno,sal,comm, rank() over(order by comm desc) rnk from emp;
EMPNO SAL RNK
---------- ---------- -------
7019 1
7546 1
7839 45100 3
7566 5994 4
7782 25881 5
7369 8900 6
7029 8900 6
2) DENSE_RANK
It provides a rank to record based on some column value. In case if a tie of 2 records occurs at position N then the two record positions will be N and give N+1 to the next record
select empno,sal,dense_rank() over(order by sal desc) den_rnk from emp;
Example
EMPNO SAL DEN_RNK
---------- ---------- --------------
7019 1
7546 1
7839 45100 2
7566 5994 3
7782 25881 4
7369 1500 5
7029 1500 5
7698 1500 5
7654 10558 6
7900 6885 7
3) ROW_NUMBER
It gives a running serial number to a record.
Example
select empno,sal,comm, Row_number() over(order by comm desc) row_num from emp;
EMPNO SAL ROW_NUM
---------- ---------- -------------------
7019 1
7546 2
7839 45100 3
7566 5994 4
7782 25881 5
7369 8900 6
7029 8900 7
4) LEAD
This command computes an expression on the next row is to return the value to the current row.
Example
select empno,sal,comm,Lead(comm , 1,0) over(order by comm desc) lead from emp;
EMPNO SAL COMM LEAD
---------- ---------- ---------- ---------- --------
7019 18224
7546 8600 5000
7839 45100 5000 2975
7566 5994 2975 2450
7782 25881 2450 1500
7369 8900 1500 1500
7029 1500 1500
7698 30858 1500 1400
7654 10558 1400 1250
5) LAG
This command computes an expression on the previous row is to return the value to the current row.
Example
select empno,sal,comm,Lag(comm,1,0) over(order by comm desc) Lag from emp;
EMPNO SAL COMM LAG
---------- ---------- ---------- ----------
7019 18224 0
7546 8600
7839 45100 5000
7566 5994 2975 5000
7782 25881 2450 2975
7369 8900 1500 2450
7029 1500 1500
7698 30858 1500 1500
7654 10558 1400 1500
7900 6885 1250 1400
8457 45000 650 1250
6) FIRST_VALUE
This command picks the first record from the partition after doing order by and the first record is returned.
Example
SELECT EMPLOYEE_ID, HIRE_DATE, DEPARTMENT_ID, FIRST_VALUE(HIRE_DATE)
OVER(PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) FROM EMPLOYEES WHERE DEPARTMENT_ID IN(10,20,30,40,50);
EMPLOYEE_ID HIRE_DATE DEPARTMENT_ID FIRST_VAL
----------- --------- ------------- ------------------------- ---------------
200 17-SEP-03 10 17-SEP-03
201 17-FEB-04 20 17-FEB-04
202 17-AUG-05 20 17-FEB-04
114 07-DEC-02 30 07-DEC-02
119 10-AUG-07 30 07-DEC-02
203 07-JUN-02 40 07-JUN-02
122 01-MAY-03 50 01-MAY-03
137 14-JUL-03 50 01-MAY-03
141 17-OCT-03 50 01-MAY-03
7) LAST_VALUE
This command picks the last record from the partition after doing the order by and the last record is returned.
Example
SELECT EMPLOYEE_ID, HIRE_DATE, LAST_VALUE(HIRE_DATE) OVER() FROM EMPLOYEES WHERE DEPARTMENT_ID IN(10,20,30,40,50) ORDER BY HIRE_DATE;
EMPLOYEE_ID HIRE_DATE DEPARTMENT_ID LAST_VALU
----------- --------- ------------- -------------------------- ---------
203 07-JUN-02 40 08-MAR-08
114 07-DEC-02 30 08-MAR-08
122 01-MAY-03 50 08-MAR-08
115 18-MAY-03 30 08-MAR-08
137 14-JUL-03 50 08-MAR-08
200 17-SEP-03 10 08-MAR-08
141 17-OCT-03 50 08-MAR-08
184 27-JAN-04 50 08-MAR-08
192 04-FEB-04 50 08-MAR-08
201 17-FEB-04 20 08-MAR-08
8) CUBE & ROLLUP
Both these command is used in the ‘group by’ clause.
Example
SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10,20,30,40) GROUP BY CUBE(DEPARTMENT_ID);
Output:-
DEPARTMENT_ID TOTAL
----------------------- ----------
55017.75
10 4300
20 19000
30 25217.75
40 6500
Example
SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10,20,30,40) GROUP BY ROLLUP(DEPARTMENT_ID);
DEPARTMENT_ID TOTAL
----------------------- ---------
10 4300
20 19000
30 25217.75
40 6500
55017.75
It provides a rank to record based on some column value. In case if a tie of 2 records occurs at position N then the two record positions will be N and give N+2 to the next record.
Example
select empno,sal,comm, rank() over(order by comm desc) rnk from emp;
EMPNO SAL RNK
---------- ---------- -------
7019 1
7546 1
7839 45100 3
7566 5994 4
7782 25881 5
7369 8900 6
7029 8900 6
2) DENSE_RANK
It provides a rank to record based on some column value. In case if a tie of 2 records occurs at position N then the two record positions will be N and give N+1 to the next record
select empno,sal,dense_rank() over(order by sal desc) den_rnk from emp;
Example
EMPNO SAL DEN_RNK
---------- ---------- --------------
7019 1
7546 1
7839 45100 2
7566 5994 3
7782 25881 4
7369 1500 5
7029 1500 5
7698 1500 5
7654 10558 6
7900 6885 7
3) ROW_NUMBER
It gives a running serial number to a record.
Example
select empno,sal,comm, Row_number() over(order by comm desc) row_num from emp;
EMPNO SAL ROW_NUM
---------- ---------- -------------------
7019 1
7546 2
7839 45100 3
7566 5994 4
7782 25881 5
7369 8900 6
7029 8900 7
4) LEAD
This command computes an expression on the next row is to return the value to the current row.
Example
select empno,sal,comm,Lead(comm , 1,0) over(order by comm desc) lead from emp;
EMPNO SAL COMM LEAD
---------- ---------- ---------- ---------- --------
7019 18224
7546 8600 5000
7839 45100 5000 2975
7566 5994 2975 2450
7782 25881 2450 1500
7369 8900 1500 1500
7029 1500 1500
7698 30858 1500 1400
7654 10558 1400 1250
5) LAG
This command computes an expression on the previous row is to return the value to the current row.
Example
select empno,sal,comm,Lag(comm,1,0) over(order by comm desc) Lag from emp;
EMPNO SAL COMM LAG
---------- ---------- ---------- ----------
7019 18224 0
7546 8600
7839 45100 5000
7566 5994 2975 5000
7782 25881 2450 2975
7369 8900 1500 2450
7029 1500 1500
7698 30858 1500 1500
7654 10558 1400 1500
7900 6885 1250 1400
8457 45000 650 1250
6) FIRST_VALUE
This command picks the first record from the partition after doing order by and the first record is returned.
Example
SELECT EMPLOYEE_ID, HIRE_DATE, DEPARTMENT_ID, FIRST_VALUE(HIRE_DATE)
OVER(PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) FROM EMPLOYEES WHERE DEPARTMENT_ID IN(10,20,30,40,50);
EMPLOYEE_ID HIRE_DATE DEPARTMENT_ID FIRST_VAL
----------- --------- ------------- ------------------------- ---------------
200 17-SEP-03 10 17-SEP-03
201 17-FEB-04 20 17-FEB-04
202 17-AUG-05 20 17-FEB-04
114 07-DEC-02 30 07-DEC-02
119 10-AUG-07 30 07-DEC-02
203 07-JUN-02 40 07-JUN-02
122 01-MAY-03 50 01-MAY-03
137 14-JUL-03 50 01-MAY-03
141 17-OCT-03 50 01-MAY-03
7) LAST_VALUE
This command picks the last record from the partition after doing the order by and the last record is returned.
Example
SELECT EMPLOYEE_ID, HIRE_DATE, LAST_VALUE(HIRE_DATE) OVER() FROM EMPLOYEES WHERE DEPARTMENT_ID IN(10,20,30,40,50) ORDER BY HIRE_DATE;
EMPLOYEE_ID HIRE_DATE DEPARTMENT_ID LAST_VALU
----------- --------- ------------- -------------------------- ---------
203 07-JUN-02 40 08-MAR-08
114 07-DEC-02 30 08-MAR-08
122 01-MAY-03 50 08-MAR-08
115 18-MAY-03 30 08-MAR-08
137 14-JUL-03 50 08-MAR-08
200 17-SEP-03 10 08-MAR-08
141 17-OCT-03 50 08-MAR-08
184 27-JAN-04 50 08-MAR-08
192 04-FEB-04 50 08-MAR-08
201 17-FEB-04 20 08-MAR-08
8) CUBE & ROLLUP
Both these command is used in the ‘group by’ clause.
Example
SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10,20,30,40) GROUP BY CUBE(DEPARTMENT_ID);
Output:-
DEPARTMENT_ID TOTAL
----------------------- ----------
55017.75
10 4300
20 19000
30 25217.75
40 6500
Example
SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10,20,30,40) GROUP BY ROLLUP(DEPARTMENT_ID);
DEPARTMENT_ID TOTAL
----------------------- ---------
10 4300
20 19000
30 25217.75
40 6500
55017.75
No comments:
Post a Comment