Limit the number of rows to join to, in mysql

Question

So I want to join two tables together, but for each row in the first table, I only want to join it to the top 8 matching rows in the other table, ordered by one of the columns in that table. Any clever syntax I can use, or do I need to get messy with subqueries?

Answer 1

Have a look at

How to select the first/least/max row per group in SQL

Section Select the top N rows from each group

This is a slightly harder problem to solve. Finding a single row from each group is easy with SQL’s aggregate functions (MIN(), MAX(), and so on). Finding the first several from each group is not possible with that method because aggregate functions only return a single value. Still, it’s possible to do.