mysql - Limit results in join query -
this question has answer here:
i have 2 tables: list of cities, , list of categories 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 top 3 categories each city (sort total).
what tried
when try query, tells me field v.id
unknown subquery.
select * tv_village v inner join ( select * tv_village_category vc2 vc2.village_id = v.id , vc2.total > 0 order vc2.total desc limit 3 ) vc order v.id, vc.total desc
i need add performances matters, , tables bit huge (36k cities , 1m categories).
regards,
one method try variables:
select v.* (select v.*, (@rn := if(@v = village_id, @rn + 1, if(@v := village_id, 1, 1) ) ) seqnum tv_village v cross join (select @rn := 0, @v := '') params order village, total desc ) v seqnum <= 3;
this can take advantage of index on tv_village(village_id, total)
.
Comments
Post a Comment