To implement top N in Oracle, select the first few records ____oracle

Example 1: Query the manager (manager) in each department who has the lowest payroll HR

SELECT * FROM
(
Select T1.MANAGER_ID as employee number, T2.first_name as employee name, t2.salary as salary
From Hr.departments T1
Join Hr.employees T2 on t1.manager_id=t2.employee_id
ORDER BY T2.salary

)
where Rownum=1;
*******************************************************************************

where Rownum=1 represents the first row, Rownum<=1, too, but . rownum=2, >2, =2 What's not going to work.

can only use <=, <, = 1, and sorted well after a subquery

Reason:

1. Because RowNum is a pseudo column that adds a result set, that is, the result set is first followed by a column (emphasis: The result set is first).

It is added, so the order is the original rownum value.

2.rownum J is the serial number that matches the result of the condition. It's always lined up from 1. So your chosen result cannot be 1, and there are other values greater than 1.

3. He does not exist and will be reassigned only after each select, so add a subquery to reassign RowNum

*******************************************************************************

Example 2: Query for the highest-paid 5-person information, sorted by payroll: HR

SELECT * from

(

Select T2.department_name as department name,

T1.first_name as employee name, t1.salary as salary

From employees T1

Left outer JOIN departments T2 on T2.DEPARTMENT_ID=T1.DEPARTMENT_ID

ORDER by t1.salary desc

)

where rownum<=5

*******************************************************************************

but what to do if you want to extract the data of row m or the middle of a few data. Rownum=m is not going to work.

nested subqueries can be used here

Example 3: Query salary from high to low ranked 第2-5个 information hr.

Select Department Name, employee name, payroll from--First floor
(
Select RowNum as no, department name, employee name, payroll from--the second level, where the rownum is alias, otherwise the outer layer and its own duplicate
(
Select T2.department_name as department name --third floor
T1.first_name as employee name, t1.salary as salary
From Employees T1
Left OUTER join departments T2 on T2.DEPARTMENT_ID=T1.DEPARTMENT_ID
ORDER BY t1.salary Desc
)
Where rownum<=5--five elements before taking out
)
where no>=2 and no<=5--Remove 2-5 number

Example 4: Extract the X record at the beginning of section m of the recordset sorted in some way

similar to the above, the third layer rownum<=n here n must satisfy N >= (M + X-1)

The most outward condition is changed to No Bwtween M and (m+x-1).