[Oracle] efficient SQL statement analysis function (2)-max ()

 

If you want to obtain the employee record with the highest salary for each department, you can use the following four methods:

Create an example table first

Create Table EMP
As
Select * from Scott. EMP;

ALTER TABLE EMP
Add constraint emp_pk
Primary Key (empno );

Create Table Dept
As
Select * from Scott. Dept;

Alter table Dept
Add constraint dept_pk
Primary Key (deptno );

Method 1. Each row in EMP is compared by Max, which takes a long time.

Select * from EMP emp1 where emp1.sal = (select max (emp2.sal) from EMP emp2 where emp2.deptno = emp1.deptno)

Method 2: first query the max Sal and associate it with the EMP table. If the logic is complex, more code is generated.

Select * from EMP emp1, (select deptno, max (SAL) maxsal from EMP emp2 group by emp2.deptno) emp3 where emp1.deptno = emp3.deptno and emp1.sal = emp3.maxsal

Method 3. Use the max Analysis Function

Select deptno, maxsal, empno from (
Select max (SAL) over (partition by deptno) maxsal, EMP. * from EMP) emp2
Where emp2.sal = emp2.maxsal

Method 4: Use the dense_rank analysis function. If a department may have multiple maximum salaries, you cannot use the row_number () analysis function.

Select deptno, Sal, empno from (
Select EMP. *, dense_rank () over (partition by deptno order by Sal DESC) rownumber from EMP) emp2
Where rownumber = 1

The result is as follows:

10 5000.00 7839
20 3000.00 7788
20 3000.00 7902
30 2850.00 7698