Oracle Row_number ()

Asked to query the maximum sample information for each user, it occurred to me that MS SQL provided row_number () over (partition by column DESC), so Oracle might also exist,

My table structure is as follows:

CreateTableNeogoodsrule (IDNumber (a) not null, Personalid NVARCHAR2 (+), Ct_smp_type NVARCHAR2 (+)) Tablespace vgsm pctfree  initrans 1 maxtrans 255 Storage (initial 64K minextents 1
                      
                        Maxextents Unlimited); 
                                     

The data are as follows:

InsertIntoNeogoodsrule (ID, Personalid, Ct_smp_type)VALUES (2270,‘JYZ‘,‘Raw materials‘);InsertIntoNeogoodsrule (ID, Personalid, Ct_smp_type)VALUES (2271,‘JYZ‘,‘Accessories‘);InsertIntoNeogoodsrule (ID, Personalid, Ct_smp_type)VALUES (2359,‘SYSTEM‘,‘Packaging (inside)‘);InsertIntoNeogoodsrule (ID, Personalid, Ct_smp_type)VALUES (2360,‘SYSTEM‘,‘Packaging (outside)‘);InsertIntoNeogoodsrule (ID, Personalid, Ct_smp_type)VALUES (2361,‘SYSTEM‘,‘Raw materials‘);InsertIntoNeogoodsrule (ID, Personalid, Ct_smp_type)VALUES (2362,‘SYSTEM‘,‘Finished‘);InsertIntoNeogoodsrule (ID, Personalid, Ct_smp_type)VALUES (2363,‘SYSTEM‘,‘Stability (acceleration)‘);InsertIntoNeogoodsrule (ID, Personalid, Ct_smp_type)VALUES (2364,‘SYSTEM‘,‘Stability (long-term)‘);InsertIntoNeogoodsrule (ID, Personalid, Ct_smp_type)VALUES (2365,‘SYSTEM‘,‘Accessories‘);InsertInto Neogoodsrule (ID, Personalid, Ct_smp_type) values ( Span style= "color: #800000; Font-weight:bold; " >2354,  ly ' 

The Row_number () syntax is as follows:

1. Row_number () Over (Order by column ASC) first returns a serial number for each record in ascending order of column columns:

Select Personalid,row_number () over (order by personalid ASC) RN from Neogoodsrule

2, Row_number () over (partition by Column1 ORDER by column2 ASC) is grouped by column1, followed by Column2 in ascending order of grouped data

Select Personalid,ct_smp_type,row_number () over (partition by Personalid ORDER by ct_smp_type ASC) RN from Neogoodsrule

As a result, the required SQL code to begin with IS as follows

SELECT * FROM (select Personalid,ct_smp_type,row_number () over (partition by Personalid ORDER by ct_smp_type ASC) RN from N Eogoodsrule)
where rn=1

Use the row_number () paging function instead of groupby. For example: statement 1:
Select Col1,col2 from T where col1 in (select col1 from T GROUP by COL1 have Count (*) <=2) Order

by order col2;
Statement 1 can be replaced with the following statement:
Statement 2:
Select Col1,col2 from (select *,row_number () over (partition by col1 ORDER by col2) as P_group from

t) where p_group<=2;

Row_number () over (PARTITION by COL1 ORDER by COL2)
Represents the sorting based on COL2 within the grouping according to the COL1 grouping. This value represents the sequential number of each set of internally ordered (the only consecutive

）。

----------------------------------------------------------------------------------

Select A,b,c,sum (c) over (partition by a) from T2
Get results:
A B C SUM (c) over (Partitionbya)
-- -- ------- ------------------------
H B 3 3
M a 2 4
M a 2 4
N a 3 6
N B 2 6
N B 1 6
X B 3 9
X B 2 9
X B 4 9

If you use Sum,group by then you can only get
A SUM (C)
-- ----------------------
H 3
M 4
N 6
X 9
Cannot get column B value---------------------------------------------------------------------------------------------

RANK () Over (PARTITION by COL1 ORDER by COL2)
Similar, but rank sort of time with the pie rank, can tie 2 first and then 3rd place.

Dense_rank () Over (PARTITION by COL1 ORDER by COL2)
Similar. is a sequential sort, with two second names still followed by third place.

Dept Sal Emp Row_number Rank Dense_rank
---- ------- ----- ---------- ----- ----------
10 5000.00 7839 1 1 1
10 2450.00 7782 2 2 2
10 1300.00 7934 3 3 3
20 3000.00 7788 1 1 1
20 3000.00 7902 2 1 1
20 2975.00 7566 3 3 2
20 1100.00 7876 4 4 3
20 800.00 7369 5 5 4
30 2850.00 7698 1 1 1
30 1600.00 7499 2 2 2

in the first place, you cannot use Row_number (), because if the class has two side-by-side first, row_number () returns only one result

Oracle Row_number ()