LIMIT number of rows in a JOIN between MySQL tables

Question

What I have

I have the following two tables in a MySQL database (version 5.6.35).

CREATE TABLE `Runs` (
  `Name` varchar(200) NOT NULL,
  `Run` varchar(200) NOT NULL,
  `Points` int(11) NOT NULL
) DEFAULT CHARSET=latin1;

INSERT INTO `Runs` (`Name`, `Run`, `Points`) VALUES
('John', 'A08', 12),
('John', 'A09', 3),
('John', 'A01', 15),
('Kate', 'A02', 92),
('Kate', 'A03', 1),
('Kate', 'A04', 33),
('Peter', 'A05', 8),
('Peter', 'A06', 14),
('Peter', 'A07', 5);

CREATE TABLE `Users` (
  `Name` varchar(500) NOT NULL,
  `NumberOfRun` int(11) NOT NULL
) DEFAULT CHARSET=latin1;

INSERT INTO `Users` (`Name`, `NumberOfRun`) VALUES
('John', 2),
('Kate', 1),
('Peter', 3);

ALTER TABLE `Runs`
  ADD PRIMARY KEY (`Run`);

What is my target

• John have Users.NumberOfRun=2, so I will extract the 2 top records from Runs table
• Kate have Users.NumberOfRun=1, so I will extract the 1 top record from Runs table
• Peter have Users.NumberOfRun=3, so I will extract the 3 top records from Runs table

I would like to came to the following result

+-------+-----+--------+
| Name  | Run | Points |
+-------+-----+--------+
| John  | A01 |     15 |
| John  | A08 |     12 |
| Kate  | A02 |     92 |
| Peter | A06 |     14 |
| Peter | A05 |      8 |
| Peter | A07 |      5 |
+-------+-----+--------+

What I have tried

First of all, if it was SQL Server I would use ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ) AS [rn] function to the Runs table and then make a JOIN with the Users table on Users.NumberOfRun<=[rn].

I have read this document but it seems that PARTITONING in MySQL it is available since version 8.X, but I am using the 5.6.X version.

Finally, I have tried this query, based on this Stackoverflow answer:

SELECT t0.Name,t0.Run
FROM Runs AS t0
LEFT JOIN Runs AS t1 ON t0.Name=t1.Name AND t0.Run=t1.Run AND t1.Points>t0.Points
WHERE t1.Points IS NULL;

but it doesn't give me the row number, which is essentially for me to make a JOIN as described above.

SQL Fiddle to this example.

Answer 1

A combination of 'group_concat' and 'find_in_set', followed by the filtering using the position returned by 'find_in_set' will do the job for you.

GROUP_CONCAT will sort the data in descending order of points first.

GROUP_CONCAT(Run ORDER BY Points DESC)

FIND_IN_SET will then retrieve the number of rows you want to include in the result.

FIND_IN_SET(Run, grouped_run) BETWEEN 1 AND Users.NumberOfRun

The below query should work for you.

SELECT
  Runs.*
FROM
  Runs 
  INNER JOIN (
    SELECT
      Name, GROUP_CONCAT(Run ORDER BY Points DESC) grouped_run
    FROM
      Runs
    GROUP BY Name
  ) group_max ON Runs.Name = group_max.Name
  INNER JOIN Users ON Users.Name = Runs.Name
  WHERE FIND_IN_SET(Run, grouped_run) BETWEEN 1 AND Users.NumberOfRun
ORDER BY
  Runs.Name Asc, Runs.Points DESC;