A small Oracle subquery exercise

I was lying in bed last night watching Oracle (recently studying this). My roommate gave me a question and asked me to try it. The questions are as follows:

If the table structure is as follows, select the information of the top three persons whose score is the same (if the score is the same, it is counted as the same) and the table name is test:

NAME GRADE
------------------------------
Kate 80
Jenny 80
Daring 85
Agony 85
Xxx 90
Yyy 60

 

I thought about the following points yesterday:

1. First, you must sort them.

2. subquery

3. Since Oracle does not have TopN, you can only use the rownum pseudo column.

4. Because duplicate scores can be obtained, consider using distinct or grouping.

 

After testing, my answer is as follows:

Select * from test
Where grade in (

Select grade from (

Select distinct grade from test

Order by grade desc

)

Where rownum <4

)

Order by grade desc;

 

Or

Select * from test
Where grade in (

Select grade from (

Select grade from test

Group by grade

Order by grade desc

)

Where rownum <4

)

Order by grade desc;

 

The above two methods have similar ideas, except that the preceding distinct keyword is used to filter duplicates. The following methods are implemented by grouping. The idea of implementation remains unchanged.