Find out the top 3 students in each discipline SQL

Find the top 3 student information for each subject SQL, there are 2 ways, one is to use the SQL Row_number () over () function, and the other is to use subqueries,

Table is designed as follows

If the results of various disciplines are not considered, there are two ways to make inquiries,

1. Row_number () over () function

SELECT * FROM (
Select Row_number () over (partition by Coursename ORDER by score Desc) as rownum,* from MyTest
) as a
Where RowNum < 4
ORDER BY Coursename

Results such as

2. Sub-query

SELECT * FROM MyTest m
where ID in (select top 3 ID from MyTest n where n.coursename = m.coursename ORDER BY score Desc)
ORDER BY Coursename,score Desc

Results such as

It can be seen that when the first three points of the various disciplines are not the same, the above 2 methods can be, but if an account has the same score,

The results will be inconsistent, such as Zhang San and Andy Lau's math scores are 70 points, the first three identified, including Andy Lau, while the second query the top three includes Zhang San

To solve these problems, you can use the SQL rank () over () or Dense_rank () over () function to query,

Specifically, which of the 2 methods to use to see the specific results requirements, you can first understand the specific use of these 2 functions and then decide which is more appropriate,

Here I take the rank () over () function as an example, the workaround is as follows:

1.

SELECT * FROM (
Select Rank () over (partition by Coursename ORDER by score Desc) as rownum,* from MyTest
) as a
Where RowNum < 4
ORDER BY Coursename

Results such as

At this point you can see the math lesson, the score is 70 of Andy Lau and Zhang San have been queried

If you use the Dense_rank () over () function, the statement is as follows

SELECT * FROM (
Select Dense_rank () over (partition by Coursename ORDER by score Desc) as rownum,* from MyTest
) as a
Where RowNum < 4
ORDER BY Coursename

Results such as

The reason is that Dense_rank () over () and rank () over () are treated differently for duplicate data numbers,

Rank () skips numbers when encoding the next record when it encounters duplicate records, and Dense_rank () over () does not,

Use Rank () over () as follows

Select Rank () over (partition by Coursename ORDER by score Desc) as rownum,* from MyTest

Results such as

Pay attention to the rules of the mathematics subject by the score rank

If you use Dense_rank () over (),

Select Dense_rank () over (partition by Coursename ORDER by score Desc) as rownum,* from MyTest

Results such as

In addition, for sub-query mode, for repeating scores of records, if we query the top 3 of each subject also want to query, you can use the following improved statements, such as

With A as (
Select distinct Coursename,score from MyTest m
where ID in (select top 3 ID from MyTest n where n.coursename = m.coursename ORDER BY score Desc)
--order by Coursename,score Desc
)
Select m.*
From MyTest m join a N
On m.coursename = N.coursename
and M.score = N.score
ORDER BY Coursename,score Desc

Results such as

This is similar to the case with the SQL function rank () over ().

Find out the top 3 students in each discipline SQL