Analysis of the number of automatic continuity or by metrics, the Dense_Rank () function sometimes we want to automatically calculate the number of adds to the list, determine which location to query
It is necessary to use
Rank (), or Dense_
Rank () function, the following uses the example, don't correct
Table EMP has a field dePTNO, ENAME, SAL, the following is a Sequential Order that does not exclude the same number.
Select Deptno,
ENAME,
Sal,
Rank () over (Partition by Deptno Order By Sal "
Rank "
From EMP
WHERE deptno = 30;
The results show are as follows:
Deptno ename
Rank
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
30 James 950 1
30 Ward 1250 2
30 martin 1250 2
30 Turner 1500 4
30 Allen 1600 5
30 Blake 2850 6
What if I want is the same, even if it is a continuous number ??
Select Deptno,
ENAME,
Sal,
DENSE_
Rank () over (Partition by Deptno Order By Sal "
Rank "
From EMP
WHERE deptno = 30;
The result will be as follows
Deptno ename
Rank
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
30 James 950 1
30 Ward 1250 2
30 martin 1250 2
30 Turner 1500 3
30 Allen 1600 4
30 Blake 2850 5
Sometimes we need is to determine the minimum or maximum or average or other requirement of a large number of groups.
Use the following methods:
Select Deptno,
ENAME,
Sal,
MIN (SAL) Keep (Dense_
Rank First Order By Sal) Over (Partition By Deptno "Lowest",
Max (SAL) Keep (DENSE_
Rank Last Order By Sal (Partition By Deptno "Highest"
From EMP
Order by deptno, sal;
The results are as follows:
Deptno Ename Sal Lowest Highest
---------- --------------------------------------
10 miller 1300 1300 5000
10 Clark 2450 1300 5000
10 king 5000 1300 5000
20 Smith 800 800 3000
20 Adams 1100 800 300020 Jones 2975 800 3000
20 scott 3000 800 3000
20 Ford 3000 800 3000
30 James 950 950 2850
30 Ward 1250 950 2850
30 martin 1250 950 2850
Deptno Ename Sal Lowest Highest
---------- --------------------------------------
30 Turner 1500 950 2850
30 Allen 1600 950 2850
30 Blake 2850 950 2850
