Oracle subquery, sorted in descending order, taking the first n

For example, there is a student table:

 

Id student_name

1 S1

2 S2

03S3

04S4

05S5

06S6

07S7

08S8

09S9

 

These IDS are inconsistent. For example, I want to change IDs to 1, 2, 3 (instead of 03), 4, 5, 6, 7, 8, 9.

 

You can perform the following operations:

Update student a set a. ID = (select substr (B. ID,-1) from student B where B. ID = A. ID)
Where a. ID> 2;

Commit;

The result is changed:

 

Id student_name

1 S1

2 S2

3S3

4S4

5S5

6S6

7S7

8S8

9S9

 

Then, you can use the subquery method to display the first six records in reverse order:

 

Select * from (select * from student order by id desc)
Where rownum <= 6;

 

Result:

 

Id student_name

9 S9

8 S8

7 S7

6 S6

5 S5

4 S4