Limit in subquery in MySQL

Question

I need to make an SQL query in MySQL, the query should select from two tables store and item, store has one or more items, the query should select from these two tables ordered by created date from store table and updated date from item table, with maximum number of items is 10 of each store, means no more than 10 items of each store if the store has more than 10 items, I think about doing this query:

select * 
from store s, item i
where s.store_id = i.store_id
  and i.item_id in (select i1.item_id 
                    from item i1 
                    where i1.store_id = s.store_id  
                    limit 10)
group by s.store_id, i.item_id
order by s.created_at, i.updated_at

but I got an error

This version of MySQL doesn't yet support limit in subquery

Is there any way around to do that?

Check out this previous answer http://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu — twoleggedhorse, Mar 03 '14 at 10:54

score 0 · Answer 1 · answered Mar 03 '14 at 10:55

You might have to upgrade your MySQL version.

If not sure if this will work but worth to give it a try

select s.*,i.* 
from store s
JOIN item i on s.store_id=i.store_id
JOIN (select i1.item_id from item i1 where i1.store_id = s.store_id limit 10) 
     itemids ON i.item_id=itemids.item_id
group by s.store_id, i.item_id
order by s.created_at, i.updated_at

Im doing a join on the subselect instead and joining them on i.item_id

Limit in subquery in MySQL

1 Answers1