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

This may not be the best solution, but say you're joining on ID, you can use a subquery in your where clause.

select from table1 where id in (select top 8 id from table2 order by column1 desc)

Answer 2

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.