Group by top n application scenarios, grouptop

Group by top n application scenarios, grouptop

Due to work reasons, some time ago, we encountered such a problem: the game role can be used together, but only three roles can be retained under the same account in each zone. The problem is that the excavator technology is strong?

Some may still fail to understand the Application Scenario. For example:

The above data scenario requires the scores of the first two students in a single subject.

How can we solve this problem now?

The analysis shows that we need to find the top two rankings on each subject.

Sorry, both Oracle and MSSQL have their own rownumber () over (partition by col1 order by col2)

Row_number () is similar to rownum and more powerful (it can be sorted from 1 on each group );

Rank () is the Skip sorting. When there are two second names, the next is the fourth name (also in each group );

Dense_rank () l is a continuous sorting, with two second names still followed by the third. In contrast, row_number does not have repeated values.

MySQL does not seem to have such a function, and there are no Over functions. If you want to, you can write an Over function, but we will not discuss it here. I only say one statement that can be directly queried.

SELECT * FROM (select *, @ num: = if (@ group = 'Course', @ num + 1, 1) as row_number, @ group: = course as sub_group
From Points order by course, point desc) as x
WHERE x. row_number <= 2;

There are several points in the SQL statement to explain that @ num is equivalent to a sort code given by ourselves, which is then separated by sub_group, that is to say, a group contains a unique non-repeated sorting code, with this sorting code, we can use this sorting code in this large result set as the filtering condition for the first few students to output the final data.

Note that the sorting codes in each group are all auto-incrementing, and the former rank () and dense_rank () functions are not implemented.

Since it is said that MSSQL has such a function, it is always necessary to pull it out.

SELECT [name], [course], [point]
FROM
(SELECT [name], [course], [point],
ROW_NUMBER () OVER (partition by [course] order by [point] DESC) AS RowNumber
FROM [dbo]. [Points]) AS X
Where x. RowNumber <= 2

This topic has been discussed here. If you have any questions or have better implementation methods, please @ me.

Zookeeper