Rownum pseudo line number – leaderboard – Pagination

Time:2019-12-22

 Rownum pseudo line number – leaderboard – Pagination

1. Rownum is a unique feature of Oracle database. For each query (including subquery), it will generate a rownum to number the query

2. Each rownum is only valid for the current select query and can be displayed by alias

Example: select rownum, EMP. * from EMP; — specify the column for the table name (* specify the column by alias before * or specify the column for the table name)

Example: query the top five with the highest salary

–From, where, group by, having, select, select

Sort before filter

(error) select rownum, EMP. * from EMP where rownum < = 5 order by Sal DESC; — filter first and then sort

Select * from (select * from EMP order by Sal DESC) where rownum < = 5; — sort first, then filter

 

Ranking List
Example:

–Find out the names and wages of the top 5 employees

–Understanding: sort first, query the first 5 items through rownum, and check whether the salary exists in the first 5 items

–This example contains three queries, three rownum in total

select ename ,sal from emp

where sal in(select * from ( select sal from emp order by sal desc) where rownum<=5) and rownum<=5;

 

Example:

–Find out the top five with the lowest wage

–Note: when querying leaderboards, you may encounter boundary problems

select rownum,emp.* from emp where rownum<=5 order by sal;

–Find out the names and wages of the top five with the lowest wages

select ename,sal from emp

where sal in (select * from (select sal from emp order by sal asc ) where rownum <=5) and rownum <=5;

 

Paging query
–Pagination calculation parameters: page number, pagination size — calculate Pagination

–Two paging methods: rownum and rownumde aliases

Note: rownum does not support > and > = judgment; rownum’s alias supports > and > = judgment

 

Method 1

Example:

Query employee name, salary and employment date in pages; display 5 items on each page to query the data on page 1, 2 and 3 respectively

Method 1:

Understanding: first, query the first N pages in the sub query, and then the second main query. You need to query the rownum alias in the sub query

–First, use sub query to find 1 * 5 1-5 pages, and then use main query to find out that greater than (1-1) * 5 > 0 1-2-3-4-5 is the first page

–First, use sub query to find 2 * 51-10 pages, and then use main query to find out that greater than (2-1) * 5 > 5 6-7-8-9-10 is the second page

–First, use sub query to find 3 * 51-15 pages, and then use main query to find out that greater than (3-1) * 5 > 5 11-12-13-14-15 is the third page

select * from (select rownum rn,emp.* from emp where rownum <=1*5) where rn >(1-1)*5

select * from (select rownum rn,emp.* from emp where rownum <=2*5) where rn >(2-1)*5

select * from (select rownum rn,emp.* from emp where rownum <=3*5) where rn >(3-1)*5

 

Method two:

–First, query out all data and rownum of data in sub query, and use alias to represent, and control paging through alias in the second main query

Understanding: – first, query all data with subquery, and control paging with main query: (3-1) * 5 < = RN < = 3 * 5 page 3

–First, query all data with subquery, and control paging with main query: (2-1) * 5 < = RN < = 2 * 5 page 2

–First, query all data with subquery, and control paging with main query: (1-1) * 5 < = RN < = 1 * 5 page 1

select * from (select rownum rn,emp.* from emp ) where rn<=3*5 and rn>=(3-1)*5;

select * from (select rownum rn,emp.* from emp ) where rn<=2*5 and rn>=(2-1)*5;

select * from (select rownum rn,emp.* from emp) where rn<=1*5 and rn>=(1-1)*5;

Method 1 is more efficient

Recommended Today

[Q & A share the second bullet] MySQL search engine, after watching the tyrannical interviewer!

Hello, I’m younger brother. A few days ago, I shared the second interview question, the interview site of search engine in MySQL. This question is the interview at normal temperature. After reading it, I’m sure you will gain something in terms of database engine If you haven’t read my first share, you can refer to […]