Detailed explanation of MySQL group sorting to find the top N

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

<<:  React+Koa example of implementing file upload

>>:  How to build Jenkins+Maven+Git continuous integration environment on CentOS7

Recommend

Basic operation tutorial of files and permissions in centos

Preface Before we begin, we should briefly unders...

Use image to submit the form instead of using button to submit the form

Copy code The code is as follows: <form method...

Detailed summary of MySQL and connection-related timeouts

MySQL and connection related timeouts Preface: To...

How to design the homepage of Tudou.com

<br />I have been working in front-end for s...

Summary of the most commonly used knowledge points about ES6 new features

Table of contents 1. Keywords 2. Deconstruction 3...

Object.entries usage you don't know in JavaScript

Table of contents Preface 1. Use for...of to iter...

How is MySQL transaction isolation achieved?

Table of contents Concurrent scenarios Write-Writ...

Configure VIM as a C++ development editor in Ubuntu

1. Copy the configuration file to the user enviro...

How to add and delete unique indexes for fields in MySQL

1. Add PRIMARY KEY (primary key index) mysql>A...

Two methods to implement Mysql remote connection configuration

Two methods to implement Mysql remote connection ...

Solution to data duplication when using limit+order by in MySql paging

Table of contents summary Problem Description Ana...

Detailed explanation of reduce fold unfold usage in JS

Table of contents fold (reduce) Using for...of Us...

Commonly used English fonts for web page creation

Arial Arial is a sans-serif TrueType font distribu...

The ultimate solution for writing bash scripts with nodejs

Table of contents Preface zx library $`command` c...

Share some key interview questions about MySQL index

Preface An index is a data structure that sorts o...