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"

Create TableTsaler (userid Number(Ten), Salary Number(Ten), DeptID Number(Ten))--Add Comments to the columnsComment on columnTsaler.userid is 'Employee ID'; Comment on columntsaler.salary is 'Wages'; Comment on columnTsaler.deptid is 'Department ID';Insert  intoTsaler (work number, salary, department number)Values(1, $,1);Insert  intoTsaler (work number, salary, department number)Values(2, -,1);Insert  intoTsaler (work number, salary, department number)Values(3, $,1);Insert  intoTsaler (work number, salary, department number)Values(4, +,2);Insert  intoTsaler (work number, salary, department number)Values(5, +,2);Insert  intoTsaler (work number, salary, department number)Values(6, the,2);

View Code

Query Result:

2.1 Method One

Select Tsaler. *  from  Innerjoin(selectminas fromgroup  by DeptID) C on tsaler.salary= and Tsaler.deptid=

2.2 Method Two

Select *  from  Innerjoin(selectminas fromgroup  by DeptID) cusing (Salary,deptid)

2.3 Method Three

--row_number () sequential sortingSelectRow_number () Over(Partition byDeptIDOrder  bySalary) My_rank, deptid,userid,salary fromTsaler;--rank () (jump sort, if there are two first levels, then the third level)SelectRank () Over(Partition byDeptIDOrder  bySalary) My_rank,deptid,userid,salary fromTsaler;--Dense_rank () (sequential order, if there are two first levels, then the second level)SelectDense_rank () Over(Partition byDeptIDOrder  bySalary) My_rank,deptid,userid,salary fromTsaler;-------Solution 3Select *  from(SelectRank () Over(Partition byDeptIDOrder  bySalary) My_rank,deptid,userid,salary fromTsaler)whereMy_rank=1;Select *  from(SelectDense_rank () Over(Partition byDeptIDOrder  bySalary) My_rank,deptid,userid,salary fromTsaler)whereMy_rank=1;

Use of Oracle Partition by