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

Sample code for implementing multiple selection based on nested Table in ElementUI

Blog

Detailed explanation of MySQL group sorting to find the top N

A brief discussion on the placement of script in HTML

Install multiple versions of PHP for Nginx on Linux

How to use MySQL covering index and table return

Sample code for implementing multiple selection based on nested Table in ElementUI

Vue implements the question answering function

Newbies quickly learn the steps to create website icons

An article to give you a deep understanding of Mysql triggers

Introduction to nesting rules of html tags

Docker enables multiple port mapping commands

CentOS 6.4 MySQL 5.7.18 installation and configuration method graphic tutorial

Recommend

How to modify the default storage location of Docker images (solution)

Implementation of HTML command line interface

React realizes the whole process of page watermark effect

Analysis of the solution to Nginx Session sharing problem

New ideas for time formatting in JavaScript toLocaleString()

Detailed explanation of how to install PHP7 on Linux

Hyper-V Introduction and Installation and Use (Detailed Illustrations)

Advantages and disadvantages of MySQL indexes and guidelines for creating indexes

Recommend 60 paging cases and good practices

Docker realizes the connection with the same IP network segment

jQuery+Ajax to achieve simple paging effect

A brief discussion on size units in CSS

Detailed analysis of classic JavaScript recursion case questions

MySQL 8 new features: Invisible Indexes

mysql determines whether the current time is between the start and end time and the start and end time are allowed to be empty