Oracle row_number () over () Resolution function for efficient Paging

Copy codeThe Code is as follows:
Create table T_NEWS
(
Id number,
N_TYPE VARCHAR2 (20 ),
N_TITLE VARCHAR2 (30 ),
N_COUNT NUMBER
)

Prompt Disabling triggers for T_NEWS...
Alter table T_NEWS disable all triggers;
Prompt Loading T_NEWS...
Insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
Value (1, 'it', 'it1', 100 );
Insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
Values (2, 'Sports ', 'Love sports 1', 10 );
Insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
Values (3, 'Sports ', 'Love sports 2', 30 );
Insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
Values (4, 'it', 'it2', 300 );
Insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
Value (5, 'it', 'it3', 200 );
Insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
Values (6, 'Sports ', 'Love sports 3', 20 );
Insert into T_NEWS (ID, N_TYPE, N_TITLE, N_COUNT)
Values (7, 'Sports ', 'Love sports 4', 60 );
Commit;


Step 1: Use rownum first

-- Pagination row_number, not rownum
-- Sort by n_count from large to small, 3 entries per page
Select rownum r, t. * FROM t_news t
Where rownum <= 3
Order by t. n_count DESC
-- Question: Why is the row number messy after order?
Select rownum r, t. * FROM t_news t
-- Cause: the row number is allocated first, and then sorted by n_count.

-- So it must be sorted and the row number is generated.
Select rownum r, t. * FROM (
SELECT t. * FROM t_news t order by t. n_count DESC) t

-- Paging
-- Err
Select rownum r, t. * FROM (
SELECT t. * FROM t_news t order by t. n_count DESC) t
WHERE r between 1 AND 3

-- 1st page
Select rownum r, t. * FROM (
SELECT t. * FROM t_news t order by t. n_count DESC) t
Where rownum between 1 AND 3

-- 2nd page
Select rownum r, t. * FROM (
SELECT t. * FROM t_news t order by t. n_count DESC) t
Where rownum between 4 AND 6
-- Error: ROWNUM must start from 1!
SELECT k. * FROM (
Select rownum r, t. * FROM (
SELECT t. * FROM t_news t order by t. n_count DESC) t
) K
WHERE r BETWEEN 4 AND 6

-- Trouble, low efficiency!


* *** Step 2: Use the row_number () over () function.
Select t2. * from
(Select t. *, row_number () over (order by t. n_count desc) orderNumber from t_news t order by t. n_count desc) t2 where orderNumber between 1and 3;


**************************************** **************************************** **************************************** ****************** 88
SELECT * FROM (
SELECT t. *, row_number () over (order by n_count DESC) r
FROM t_news t
Order by t. n_count DESC
) T
WHERE r BETWEEN 4 AND 6

-- General Syntax: resolution function () over (partition by field order by field)