Detailed explanation of MySQL group sorting to find the top N

MySQL group sorting to find the top N

Table Structure

Group by grp, sort by num, and take the top 3 in each group. The output is as follows:

source code:

SELECT * FROM score AS t3  
WHERE (  
  SELECT COUNT(*) FROM score AS t1  
  LEFT JOIN score AS t2  
  ON t1.grp = t2.grp AND t1.num < t2.num  
  WHERE t1.id = t3.id 
) < 3 
ORDER BY t3.grp ASC, num DESC

In where, you can create a new variable for filtering through a subquery.

Thank you for reading, I hope it can help you, thank you for your support of this site!

You may also be interested in:

Mysql uses group by group sorting
Detailed explanation of the implementation principle of MySQL group query Group By
Implement group by based on MySQL to get the latest data of each group
How to use GROUP BY in MySQL to get the first N records
Detailed explanation of the implementation example of group ranking in Mysql tutorial

Detailed steps for implementing timeout status monitoring in Apache FlinkCEP

Blog

Detailed explanation of MySQL group sorting to find the top N

Research on Web Page Size

A quick review of CSS3 pseudo-class selectors

Detailed steps for implementing timeout status monitoring in Apache FlinkCEP

The difference between html empty link href="#" and href="javascript:void(0)"

7 interview questions about JS this, how many can you answer correctly

mysql-8.0.16 winx64 latest installation tutorial with pictures and text

MySQL 8.0.3 RC is about to be released. Let’s take a look at the changes

Introduction to the role of HTML doctype

Introduction to vim plugin installation under Linux system

Draw a heart with CSS3

Recommend

MySQL replication table details and example code

Analysis of the process of building a LAN server based on http.server

mysql solves the problem of finding records where two or more fields are NULL

Example code of how to create a collapsed header effect using only CSS

MYSQL8.0.13 free installation version configuration tutorial example detailed explanation

Solution to multiple 302 responses in nginx proxy (nginx Follow 302)

Markup Language - Phrase Elements

Detailed steps to build the TypeScript environment and deploy it to VSCode

Summary of Node.js service Docker container application practice

LayUI+Shiro implements a dynamic menu and remembers the example of menu expansion

Examples of correct use of interface and type methods in TypeScript

The viewport in the meta tag controls the device screen css

Detailed explanation of building a continuous integration cluster service based on docker-swarm

5 Tips for Protecting Your MySQL Data Warehouse

JavaScript implements the drag slider puzzle verification function (html5, canvas)