Oracle row_number ()

To query the maximum sample information corresponding to each user, it suddenly occurred that ms SQL provided row_number () over (partition by column order by column DESC), so Oracle may also exist,

My table structure is as follows:

 Create   Table  Neogoodsrule (ID  Number ( 22 ) Not   Null  , Personalid nvarchar2 (  50  ), Ct_smp_type nvarchar2 (  100  ) Tablespace vgsm pctfree 10  Initrans  1  Maxtrans  255  Storage (initial 64 K minextents  1  Maxextents unlimited ); 

The data is as follows:

 Insert   Into  Neogoodsrule (ID, personalid, ct_smp_type)  Values ( 2270 ,'  Jyz  ' , '  Raw Materials  '  );  Insert   Into  Neogoodsrule (ID, personalid, ct_smp_type)  Values ( 2271 , '  Jyz  ' , ' Auxiliary materials  '  );  Insert   Into  Neogoodsrule (ID, personalid, ct_smp_type)  Values ( 2359 , '  System  ' , '  Packaging Material (internal)  '  );  Insert  Into  Neogoodsrule (ID, personalid, ct_smp_type)  Values ( 2360 , '  System  ' , '  Packaging Material (external)  '  );  Insert   Into  Neogoodsrule (ID, personalid, ct_smp_type)  Values (2361 , '  System  ' , '  Raw Materials  '  );  Insert   Into  Neogoodsrule (ID, personalid, ct_smp_type)  Values ( 2362 , '  System ' , '  Finished Product  '  );  Insert   Into  Neogoodsrule (ID, personalid, ct_smp_type)  Values ( 2363 , '  System  ' , '  Stability (acceleration)  ' );  Insert   Into  Neogoodsrule (ID, personalid, ct_smp_type)  Values ( 2364 , '  System  ' , '  Stability (long-term)  '  );  Insert   Into Neogoodsrule (ID, personalid, ct_smp_type)  Values ( 2365 , '  System  ' , '  Auxiliary materials  '  );  Insert   Into  Neogoodsrule (ID, personalid, ct_smp_type)  Values (2354 , '  Ly  ' , '  Finished Product  '  );  Insert   Into  Neogoodsrule (ID, personalid, ct_smp_type)  Values ( 2355 , '  Ly ' , '  Raw Materials  ' );

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 for the column:

Select personalid, row_number () over (order by personalid ASC) Rn from neogoodsrule

2. row_number () over (partition by column1 order by column2 ASC) first groups data by column1, and then sort the grouped data in column2 ascending order.

Select personalid, ct_smp_type, row_number () over (partition by personalid order by ct_smp_type ASC) Rn from neogoodsrule

 

As a result, the SQLCodeAs follows:

select * from (select personalid, ct_smp_type, row_number () over (partition by personalid order by ct_smp_type ASC) Rn from neogoodsrule)
where Rn = 1