Use of Oracle Partition by

1.Overview

The parttion by keyword is part of the analytic function in Oracle , which differs from the aggregation function in that it can return multiple records in a group, and the child aggregation function generally has only one result that reflects the statistical value.

2. How to use

Scenario: Query the employee number with the lowest wage per department "there may be two minimum wage employees per department"

CreateTableTsaler (useridNumber (10), SalaryNumber (10), DeptIDNumber (10))--ADD comments to the columns commentOnColumnTsaler.useridIs‘Employee ID‘; CommentOnColumnTsaler.salaryIs‘Wages‘; CommentOnColumnTsaler.deptidIs‘Department ID‘;InsertIntoTsaler (work number, salary, department number)VALUES (1,200,1);InsertIntoTsaler (work number, salary, department number)VALUES (2,2000,1);InsertIntoTsaler (work number, salary, department number)VALUES (3,200,1);InsertIntoTsaler (work number, salary, department number)VALUES (4, 1000, 2); insert into Tsaler (work number, Salary, department number) values (5, 1000, 2into Tsaler (work number, payroll, Department number) values ( 2);          

Query Result:

2.1 Method One

Select Tsaler. From  join (by DeptID) C ontsaler.salary andtsaler.deptid     

2.2 Method Two

Fromjoin (by DeptID) cusing (salary,deptid)  

2.3 Method Three

--Row_number () Sequential sortingSelect Row_number ()Over (partitionby DeptIDOrderby salary) My_rank, deptid,userid,salaryFromTsaler;--Rank () (jump sort, if there are two first levels, then the third level)Select Rank ()Over (partitionby DeptIDOrderby salary) My_rank,deptid,userid,salaryFromTsaler;--Dense_rank () (sequential order, if there are two first levels, then the second level)Select Dense_rank ()Over (partitionby DeptIDOrderby salary) My_rank,deptid,userid,salaryFromTsaler;-------Solution 3Select*From (Select Rank ()Over (partitionby deptid order by Salary) my_rank,deptid,userid,salary from tsaler) =1; select * from (select Dense_rank () over (Partition by DeptID order by salary) my_rank,deptid,userid,salary from Tsaler) where my_rank=1                 

Use of Oracle Partition by