Analytical Function
Oracle includes a number of very useful functions that allow you to analyze, aggregate, and rank
vast amounts of stored data. You can use these analytical functions to find the top-n revenuegenerating
courses, compare revenues of one course with another, or compute various statistics
about students’ grades.
You will gain an appreciation of their core functionality and usefulness, particularly with regard
to the calculation of rankings or generation of moving averages, moving sums, and so on.
Analytical functions execute queries fairly quickly because they allow you to make one pass
through the data rather than write multiple queries or complicated SQL to achieve the same
result. This significantly speeds up query performance.
The general syntax of analytical functions is as follows.
analytic_function([arguments]) OVER (analytic_clause)
The OVER keyword indicates that the function operates after the results of the FROM, WHERE,
GROUP BY, and HAVING clauses have been formed.
ANALYTIC_CLAUSE can contain three other clauses: QUERY_PARTITIONING,
ORDER_BY, or WINDOWING.
[query_partition_clause] [order_by_clause [windowing_clause]]
The QUERY_PARTIONING clause allows you to
split a result into smaller subsets on which you can apply the analytical functions. The
ORDER_BY_CLAUSE is much like the familiar ordering clause; however, it is applied to the
result of an analytical function. WINDOWING_CLAUSE lets you compute moving and
accumulative aggregates—such as moving averages, moving sums, or cumulative sums—by
choosing only certain data within a specified window.
Query Processing with Analytical Functions
Analytical Function Types
whatever an analytic function does can be done by native SQL, with join and sub-queries. But the same
routine done by analytic function is always faster, or at least as fast, when compared to native SQL
How are analytic functions different from group or aggregate functions?
SELECT deptno,
COUNT(*) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30)
GROUP BY deptno;
DEPTNO DEPT_COUNT
---------------------- ----------------------
20 5
30 6
SELECT empno, deptno,
COUNT(*) OVER (PARTITION BY
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);
EMPNO DEPTNO DEPT_COUNT
---------- ---------- ----------
7369 20 5
7566 20 5
7788 20 5
7902 20 5
7876 20 5
7499 30 6
7900 30 6
7844 30 6
7698 30 6
7654 30 6
7521 30 6
How to break the result set in groups or partitions?
It might be obvious from the previous example that the clause PARTITION BY is used to break the result
set into groups. PARTITION BY can take any non-analytic SQL expression.
Some functions support the <window_clause> inside the partition to further limit the records they act
on. In the absence of any <window_clause> analytic functions are computed on all the records of the
partition clause.
The functions SUM, COUNT, AVG, MIN, MAX are the common analytic functions the result of which does
not depend on the order of the records.
Functions like LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST, FIRST VALUE, LAST, LAST VALUE
depends on order of records. In the next example we will see how to specify that.
How to specify the order of the records in the partition?
The answer is simple, by the "ORDER BY" clause inside the OVER( ) clause. This is different from the
ORDER BY clause of the main query which comes after WHERE. In this section we go ahead and
introduce each of the very useful functions LEAD, LAG, RANK, DENSE_RANK, ROW_NUMBER, FIRST,
FIRST VALUE, LAST, LAST VALUE and show how each depend on the order of the record.
The general syntax of specifying the ORDER BY clause in analytic function is:
ORDER BY <sql_expr> [ASC or DESC] NULLS [FIRST or LAST]
The syntax is self-explanatory.
ROW_NUMBER, RANK and DENSE_RANK
All the above three functions assign integer values to the rows depending on their order. That is the
reason of clubbing them together.
ROW_NUMBER( )
gives a running serial number to a partition of records. It is very useful in reporting, especially in places
where different partitions have their own serial numbers. In Query-5, the function ROW_NUMBER( ) is
used to give separate sets of running serial to employees of departments 10 and 20 based on their
HIREDATE.
SQL>SELECT empno, deptno, hiredate,
ROW_NUMBER( ) OVER (PARTITION BY
deptno ORDER BY hiredate
NULLS LAST) SRLNO
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, SRLNO;
EMPNO DEPTNO HIREDATE SRLNO
------ ------- --------- ----------
7782 10 09-JUN-81 1
7839 10 17-NOV-81 2
7934 10 23-JAN-82 3
7369 20 17-DEC-80 1
7566 20 02-APR-81 2
7902 20 03-DEC-81 3
7788 20 09-DEC-82 4
7876 20 12-JAN-83 5
8 rows selected.
SQL> cl scr
Rank
The RANK function assigns each row a unique number. However, duplicate rows
receive the identical ranking, and a gap appears in the sequence before the next
rank
SQL> SELECT EName, Deptno, Sal,
RANK()
OVER(ORDER BY Sal) EmpRank
FROM Emp
GROUP BY Deptno, EName, Sal
ORDER By Emprank;
ENAME DEPTNO SAL EMPRANK
---------- ---------- ---------- ----------
SMITH 20 800 1
JAMES 30 950 2
ADAMS 20 1100 3
MARTIN 30 1250 4
WARD 30 1250 4
MILLER 10 1300 6
TURNER 30 1500 7
ALLEN 30 1600 8
CLARK 10 2450 9
BLAKE 30 2850 10
JONES 20 2975 11
ENAME DEPTNO SAL EMPRANK
---------- ---------- ---------- ----------
FORD 20 3000 12
SCOTT 20 3000 12
KING 10 5000 14
14 rows selected.
SQL> ed
Wrote file afiedt.buf
SELECT E1.*
FROM (SELECT EName, Deptno, Sal,
RANK()
OVER(ORDER BY Sal) EmpRank
FROM Emp
GROUP BY Deptno, EName, Sal
ORDER By Emprank
) E1
* WHERE E1.EmpRank = &GRank
SQL> /
Enter value for grank: 1
ENAME DEPTNO SAL EMPRANK
---------- ---------- ---------- ----------
SMITH 20 800 1
SQL> /
Enter value for grank: 5
no rows selected
SQL> /
Enter value for grank: 4
ENAME DEPTNO SAL EMPRANK
---------- ---------- ---------- ----------
MARTIN 30 1250 4
WARD 30 1250 4
SQL> cl scr
DENSE_RANK
The ranking function DENSE_RANK assigns duplicate values the same rank.
SQL> SELECT EName, Deptno, Sal,
DENSE_RANK()
OVER(ORDER BY Sal DESC) EmpRank
FROM Emp
GROUP BY Deptno, EName, Sal
ORDER BY EmpRank;
ENAME DEPTNO SAL EMPRANK
---------- ---------- ---------- ----------
KING 10 5000 1
FORD 20 3000 2
SCOTT 20 3000 2
JONES 20 2975 3
BLAKE 30 2850 4
CLARK 10 2450 5
ALLEN 30 1600 6
TURNER 30 1500 7
MILLER 10 1300 8
MARTIN 30 1250 9
WARD 30 1250 9
ENAME DEPTNO SAL EMPRANK
---------- ---------- ---------- ----------
ADAMS 20 1100 10
JAMES 30 950 11
SMITH 20 800 12
rows selected.
SQL> cl scr
SQL> SELECT ROWNUM, E1.*
FROM (SELECT EName, Deptno, Sal,
DENSE_RANK()
OVER(ORDER BY Sal DESC) EmpRank
FROM Emp
GROUP BY Deptno, EName, Sal
ORDER BY EmpRank) E1
ORDER BY ROWNUM;
ROWNUM ENAME DEPTNO SAL EMPRANK
---------- ---------- ---------- ---------- ----------
1 KING 10 5000 1
2 FORD 20 3000 2
3 SCOTT 20 3000 2
4 JONES 20 2975 3
5 BLAKE 30 2850 4
6 CLARK 10 2450 5
7 ALLEN 30 1600 6
8 TURNER 30 1500 7
9 MILLER 10 1300 8
10 MARTIN 30 1250 9
11 WARD 30 1250 9
ROWNUM ENAME DEPTNO SAL EMPRANK
---------- ---------- ---------- ---------- ----------
12 ADAMS 20 1100 10
13 JAMES 30 950 11
14 SMITH 20 800 12
14 rows selected.
SQL> SELECT ROWNUM, E1.*
FROM (SELECT Ename, Sal
FROM Emp
ORDER BY Sal DESC
) E1
WHERE ROWNUM <= 5;
ROWNUM ENAME SAL
---------- ---------- ----------
1 KING 5000
2 FORD 3000
3 SCOTT 3000
4 JONES 2975
5 BLAKE 2850
SQL> ed
Wrote file afiedt.buf
SELECT ROWNUM, E1.*
FROM (SELECT Ename, Sal
FROM Emp
ORDER BY Sal DESC
) E1
WHERE ROWNUM <= 6
SQL> ed
Wrote file afiedt.buf
SELECT ROWNUM, E1.*
FROM (SELECT EName, Deptno, Sal,
DENSE_RANK()
OVER(ORDER BY Sal DESC) EmpRank
FROM Emp
GROUP BY Deptno, EName, Sal
ORDER BY EmpRank) E1
WHERE EmpRank <= 5
ORDER BY ROWNUM
SQL> /
ROWNUM ENAME DEPTNO SAL EMPRANK
---------- ---------- ---------- ---------- ----------
1 KING 10 5000 1
2 FORD 20 3000 2
3 SCOTT 20 3000 2
4 JONES 20 2975 3
5 BLAKE 30 2850 4
6 CLARK 10 2450 5
6 rows selected.
SQL> cl scr
SQL> SELECT
DENSE_RANK()
OVER(ORDER BY Ename) RollNo,
EName, Deptno, Sal
FROM Emp
GROUP BY Deptno, EName, Sal
ORDER BY RollNo;
SQL> SELECT
DENSE_RANK()
OVER(ORDER BY Ename) RollNo,
EName, Sal,
DENSE_RANK()
OVER(ORDER BY Sal DESC) RankSal,
HireDate,
DENSE_RANK()
OVER(ORDER BY HireDate) SeniorRank,
DENSE_RANK()
OVER(ORDER BY HireDate DESC) JuniorRank
FROM Emp
GROUP BY Deptno, EName, Sal, HireDate
ORDER BY RollNo;
SELECT
EName, Sal,
DENSE_RANK()
OVER(ORDER BY Sal DESC) RankSal
FROM Emp
GROUP BY EName, Sal
ORDER BY RankSal
SQL> SELECT EName, Deptno, Sal,
RANK()
OVER(PARTITION BY DeptNo
ORDER BY Sal DESC) "TOP Sal"
FROM Emp
ORDER BY Deptno, Sal DESC;
.
SQL> /
SQL> SELECT ROWNUM, E1.*
FROM (SELECT EName, Deptno, Sal,
DENSE_RANK()
OVER(ORDER BY Sal DESC) EmpRank
FROM Emp
GROUP BY Deptno, EName, Sal
ORDER BY EmpRank) E1
WHERE E1.EmpRank <= 5
ORDER BY ROWNUM;
ROWNUM ENAME DEPTNO SAL EMPRANK
---------- ---------- ---------- ---------- ----------
1 KING 10 5000 1
2 FORD 20 3000 2
3 SCOTT 20 3000 2
4 JONES 20 2975 3
5 BLAKE 30 2850 4
6 CLARK 10 2450 5
6 rows selected.
SQL> SELECT ROWNUM, E1.*
FROM (SELECT EName, Deptno, HireDate,
DENSE_RANK()
OVER(ORDER BY HireDate) HireRank
FROM Emp
GROUP BY Deptno, EName, HireDate
ORDER BY HireRank) E1
WHERE E1.HireRank <= 5
ORDER BY ROWNUM;
ROWNUM ENAME DEPTNO HIREDATE HIRERANK
---------- ---------- ---------- --------- ----------
1 SMITH 20 17-DEC-80 1
2 ALLEN 30 20-FEB-81 2
3 WARD 30 22-FEB-81 3
4 JONES 20 02-APR-81 4
5 BLAKE 30 01-MAY-81 5
LEAD and LAG
LEAD has the ability to compute an expression on the next rows (rows which are going to come after the
current row) and return the value to the current row. The general syntax of LEAD is shown below:
LEAD (<sql_expr>, <offset>, <default>) OVER (<analytic_clause>)
<sql_expr> is the expression to compute from the leading row.
<offset> is the index of the leading row relative to the current row.
<offset> is a positive integer with default 1.
<default> is the value to return if the <offset> points to a row outside the partition range.
The syntax of LAG is similar except that the offset for LAG goes into the previous rows.
SQL> SELECT Ename, HireDate, Sal,
LAG(Sal, 1, 0)
OVER(ORDER BY HireDate) PreSal
FROM Emp;
ENAME HIREDATE SAL PRESAL
---------- --------- ---------- ----------
SMITH 17-DEC-80 800 0
ALLEN 20-FEB-81 1600 800
WARD 22-FEB-81 1250 1600
JONES 02-APR-81 2975 1250
BLAKE 01-MAY-81 2850 2975
CLARK 09-JUN-81 2450 2850
TURNER 08-SEP-81 1500 2450
MARTIN 28-SEP-81 1250 1500
KING 17-NOV-81 5000 1250
JAMES 03-DEC-81 950 5000
FORD 03-DEC-81 3000 950
ENAME HIREDATE SAL PRESAL
---------- --------- ---------- ----------
MILLER 23-JAN-82 1300 3000
SCOTT 09-DEC-82 3000 1300
ADAMS 12-JAN-83 1100 3000
14 rows selected.
SQL> ed
Wrote file afiedt.buf
SELECT Ename, HireDate, Sal,
LAG(Sal, 1, 0)
OVER(ORDER BY HireDate) PreSal1,
LAG(Sal, 2, 0)
OVER(ORDER BY HireDate) PreSal2,
LAG(Sal, 3, 0)
OVER(ORDER BY HireDate) PreSal3,
LAG(Sal, 4, 0)
OVER(ORDER BY HireDate) PreSal4
FROM Emp
SQL> /
SQL> ed
Wrote file afiedt.buf
SELECT Ename, HireDate, Sal,
LEAD(Sal, 1, 0)
OVER(ORDER BY HireDate) PreSal1,
LEAD(Sal, 2, 0)
OVER(ORDER BY HireDate) PreSal2,
LEAD(Sal, 3, 0)
OVER(ORDER BY HireDate) PreSal3,
LEAD(Sal, 4, 0)
OVER(ORDER BY HireDate) PreSal4
FROM Emp
SQL> /
SQL> cl scr
FIRST and LAST
The FIRST and LAST functions can be used to return the first or last value from an ordered sequence. Say
we want to display the salary of each employee, along with the lowest and highest within their
department we may use something like.
SELECT empno,
deptno,
sal,
MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest",
MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest"
FROM emp
ORDER BY deptno, sal;
EMPNO DEPTNO SAL Lowest Highest
---------- ---------- ---------- ---------- ----------
7934 10 1300 1300 5000
7782 10 2450 1300 5000
7839 10 5000 1300 5000
7369 20 800 800 3000
7876 20 1100 800 3000
7566 20 2975 800 3000
7788 20 3000 800 3000
7902 20 3000 800 3000
7900 30 950 950 2850
7654 30 1250 950 2850
7521 30 1250 950 2850
7844 30 1500 950 2850
7499 30 1600 950 2850
7698 30 2850 950 2850
SQL>
SQL> SELECT Ename, HireDate, Sal,
Sal - LAG(Sal, 1, 0)
OVER(ORDER BY HireDate) DiffPreSal
FROM Emp;
ENAME HIREDATE SAL DIFFPRESAL
---------- --------- ---------- ----------
SMITH 17-DEC-80 800 800
ALLEN 20-FEB-81 1600 800
WARD 22-FEB-81 1250 -350
JONES 02-APR-81 2975 1725
BLAKE 01-MAY-81 2850 -125
CLARK 09-JUN-81 2450 -400
TURNER 08-SEP-81 1500 -950
MARTIN 28-SEP-81 1250 -250
KING 17-NOV-81 5000 3750
JAMES 03-DEC-81 950 -4050
FORD 03-DEC-81 3000 2050
ENAME HIREDATE SAL DIFFPRESAL
---------- --------- ---------- ----------
MILLER 23-JAN-82 1300 -1700
SCOTT 09-DEC-82 3000 1700
ADAMS 12-JAN-83 1100 -1900
14 rows selected.
SQL> ed
Wrote file afiedt.buf
SELECT Ename, HireDate, Sal,
Sal - LAG(Sal, 1, Sal)
OVER(ORDER BY HireDate) DiffPreSal
FROM Emp
SQL> /
ENAME HIREDATE SAL DIFFPRESAL
---------- --------- ---------- ----------
SMITH 17-DEC-80 800 0
ALLEN 20-FEB-81 1600 800
WARD 22-FEB-81 1250 -350
JONES 02-APR-81 2975 1725
BLAKE 01-MAY-81 2850 -125
CLARK 09-JUN-81 2450 -400
TURNER 08-SEP-81 1500 -950
MARTIN 28-SEP-81 1250 -250
KING 17-NOV-81 5000 3750
JAMES 03-DEC-81 950 -4050
FORD 03-DEC-81 3000 2050
ENAME HIREDATE SAL DIFFPRESAL
---------- --------- ---------- ----------
MILLER 23-JAN-82 1300 -1700
SCOTT 09-DEC-82 3000 1700
ADAMS 12-JAN-83 1100 -1900
14 rows selected.
SQL> SELECT Ename, HireDate, Sal,
Sal - LEAD(Sal, 1, 0)
OVER(ORDER BY HireDate) DiffNextSal
FROM Emp;
SQL> SELECT E1.Deptno, E1.DeptSalSum,
ABS(E1.DeptSalSum - NextSal)||
DECODE(NVL(SIGN(E1.DeptSalSum - NextSal), 0),
1, ' More Budget Than Next Department',
-1, ' Less Budget Than Next Department',
0, ' Terminating Department') Remarks
FROM (SELECT Deptno, SUM(Sal) DeptSalSum,
LEAD(SUM(Sal), 1, NULL)
OVER(ORDER BY Deptno) NextSal
FROM Emp
GROUP BY Deptno) E1;
DEPTNO DEPTSALSUM REMARKS
---------- ---------- ----------------------------------------
10 8750 2125 Less Budget Than Next Department
20 10875 1475 More Budget Than Next Department
30 9400 Terminating Department
SQL> cl scr
SQL> BREAK ON Deptno SKIP 1
SQL> COLUMN DaysDiff FORMAT A40
SQL> COLUMN DEPTNO FORMAT 99
SQL> COLUMN ENAME FORMAT A10
SQL> SELECT Deptno, Ename, HireDate,
LAG(HireDate, 1, NULL)
OVER(PARTITION BY Deptno
ORDER BY HireDate, Ename) Last_Hire,
NVL(HireDate - LAG(HireDate, 1, Null)
OVER (PARTITION BY Deptno
ORDER BY HireDate, Ename), 0)||' Days of Difference.' DaysDiff
FROM Emp
ORDER BY DeptNo, HireDate;
DEPTNO ENAME HIREDATE LAST_HIRE DAYSDIFF
------ ---------- --------- --------- ----------------------------------------
10 CLARK 09-JUN-81 0 Days of Difference.
KING 17-NOV-81 09-JUN-81 161 Days of Difference.
MILLER 23-JAN-82 17-NOV-81 67 Days of Difference.
20 SMITH 17-DEC-80 0 Days of Difference.
JONES 02-APR-81 17-DEC-80 106 Days of Difference.
FORD 03-DEC-81 02-APR-81 245 Days of Difference.
SCOTT 09-DEC-82 03-DEC-81 371 Days of Difference.
ADAMS 12-JAN-83 09-DEC-82 34 Days of Difference.
30 ALLEN 20-FEB-81 0 Days of Difference.
DEPTNO ENAME HIREDATE LAST_HIRE DAYSDIFF
------ ---------- --------- --------- ----------------------------------------
30 WARD 22-FEB-81 20-FEB-81 2 Days of Difference.
BLAKE 01-MAY-81 22-FEB-81 68 Days of Difference.
TURNER 08-SEP-81 01-MAY-81 130 Days of Difference.
MARTIN 28-SEP-81 08-SEP-81 20 Days of Difference.
JAMES 03-DEC-81 28-SEP-81 66 Days of Difference.
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Deptno, Sal,
FIRST_VALUE(Ename)
OVER(PARTITION BY DeptNo
ORDER BY Sal DESC) Max_Sal_Name
FROM Emp ORDER BY Deptno, Sal DESC, Ename DESC
SQL> BREAK ON Deptno DUP
SQL> /
ENAME DEPTNO SAL MAX_SAL_NA
---------- ------ ---------- ----------
KING 10 5000 KING
CLARK 10 2450 KING
MILLER 10 1300 KING
SCOTT 20 3000 FORD
FORD 20 3000 FORD
JONES 20 2975 FORD
ADAMS 20 1100 FORD
SMITH 20 800 FORD
BLAKE 30 2850 BLAKE
ALLEN 30 1600 BLAKE
TURNER 30 1500 BLAKE
ENAME DEPTNO SAL MAX_SAL_NA
---------- ------ ---------- ----------
WARD 30 1250 BLAKE
MARTIN 30 1250 BLAKE
JAMES 30 950 BLAKE
14 rows selected.
SQL> ed
Wrote file afiedt.buf
SELECT Ename, Deptno, Sal,
FIRST_VALUE(Ename)
OVER(PARTITION BY DeptNo
ORDER BY Sal DESC) Max_Sal_Name
FROM Emp
--ORDER BY Deptno, Sal DESC, Ename DESC
SQL> /
ENAME DEPTNO SAL MAX_SAL_NA
---------- ------ ---------- ----------
KING 10 5000 KING
CLARK 10 2450 KING
MILLER 10 1300 KING
FORD 20 3000 FORD
SCOTT 20 3000 FORD
JONES 20 2975 FORD
ADAMS 20 1100 FORD
SMITH 20 800 FORD
BLAKE 30 2850 BLAKE
ALLEN 30 1600 BLAKE
TURNER 30 1500 BLAKE
ENAME DEPTNO SAL MAX_SAL_NA
---------- ------ ---------- ----------
MARTIN 30 1250 BLAKE
WARD 30 1250 BLAKE
JAMES 30 950 BLAKE
14 rows selected.
SQL> ed
Wrote file afiedt.buf
SELECT Ename, Deptno, Sal,
FIRST_VALUE(Ename)
OVER(PARTITION BY DeptNo
ORDER BY Sal DESC) Max_Sal_Name
FROM Emp
ORDER BY Deptno, Sal DESC, Ename DESC
SELECT Ename, Deptno, Sal,
FIRST_VALUE(Ename)
OVER(PARTITION BY DeptNo
ORDER BY Sal DESC) Max_Sal_Name
FROM Emp
ORDER BY Deptno, Sal DESC, Ename
SQL..>
SELECT Ename, Deptno, Sal,
FIRST_VALUE(Ename)
OVER(PARTITION BY DeptNo
ORDER BY Sal DESC) Max_Sal_Name
FROM Emp
WHERE Deptno = 30
ORDER BY Deptno, Sal DESC, Ename
SQL> /
ENAME DEPTNO SAL MAX_SAL_NA
---------- ------ ---------- ----------
BLAKE 30 2850 BLAKE
ALLEN 30 1600 BLAKE
TURNER 30 1500 BLAKE
MARTIN 30 1250 BLAKE
WARD 30 1250 BLAKE
JAMES 30 950 BLAKE
6 rows selected.
SQL> ed
Wrote file afiedt.buf
SELECT Ename, Deptno, Sal,
LAST_VALUE(Ename)
OVER(PARTITION BY DeptNo
ORDER BY Sal DESC) Max_Sal_Name
FROM Emp
WHERE Deptno = 30
ORDER BY Deptno, Sal DESC, Ename
SQL> /
ENAME DEPTNO SAL MAX_SAL_NA
---------- ------ ---------- ----------
BLAKE 30 2850 BLAKE
ALLEN 30 1600 ALLEN
TURNER 30 1500 TURNER
MARTIN 30 1250 WARD
WARD 30 1250 WARD
JAMES 30 950 JAMES
6 rows selected.
SQL> ed
FIRST and LAST
The FIRST and LAST functions can be used to return the first or last value from an ordered sequence. Say
we want to display the salary of each employee, along with the lowest and highest within their
department we may use something like.
SELECT empno,
deptno,
sal,
MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest",
MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest"
FROM emp
ORDER BY deptno, sal;
EMPNO DEPTNO SAL Lowest Highest
---------- ---------- ---------- ---------- ----------
7934 10 1300 1300 5000
7782 10 2450 1300 5000
7839 10 5000 1300 5000
7369 20 800 800 3000
7876 20 1100 800 3000
7566 20 2975 800 3000
7788 20 3000 800 3000
7902 20 3000 800 3000
7900 30 950 950 2850
7654 30 1250 950 2850
7521 30 1250 950 2850
7844 30 1500 950 2850
7499 30 1600 950 2850
7698 30 2850 950 2850
No comments:
Post a Comment