Limit results in join query

Question

I have two tables: a list of cities, and a list of categories for each city.

Table tv_village

id    | name
--------------
1     | London
2     | Paris


Table tv_village_category

village_id | category   | total
-----------------------------
1          | event      | 10
1          | realestate | 15
1          | job        | 8
1          | place      | 20
2          | event      | 42
2          | realestate | 66
2          | job        | 83
2          | place      | 55

My question

I need to get the top 3 categories for each city (sort by total).

What I tried

When I try this query, it tells me that the field v.id is unknown is subquery.

    SELECT *
    FROM tv_village v
    INNER JOIN (
        SELECT *
        FROM tv_village_category vc2
        WHERE vc2.village_id = v.id
            AND vc2.total > 0
        ORDER BY vc2.total DESC
        LIMIT 3
    ) vc
    ORDER BY v.id, vc.total DESC

I need to add that performances matters, and my tables are a bit huge (36K cities and 1M categories).

Regards,

This is one of those things that'd be easy in any DB but MySql: Oracle has Lateral Joins, Sql Server has APPLY. Everything else supports one or the other of those. Add this the growing list of missing features Oracle is unlikely to add to MySql so it doesn't have to compete with itself... CTEs, Windowing functions, indexed views... I could go on. — Joel Coehoorn, Apr 20 '16 at 01:32

score 0 · Answer 1 · answered Apr 20 '16 at 01:01

One method is to try variables:

select v.*
from (select v.*,
             (@rn := if(@v = village_id, @rn + 1,
                        if(@v := village_id, 1, 1)
                       )
             ) as seqnum
      from tv_village v cross join
           (select @rn := 0, @v := '') params
      order by village, total desc
     ) v
where seqnum <= 3;

This can take advantage of an index on tv_village(village_id, total).

Limit results in join query

1 Answers1