How to use MYSQL LIMIT per person

Question

So I have a scenario where there are 1-8 people that i need to query up to 3 things they "liked" per person. I have the query set up as

SELECT liked FROM likeTable WHERE uid IN (uid1,uid2,uid3,uid4) LIMIT 12

but obviously this can potentially stop when i have 12 "likes" for uid1, leaving the rest at 0. I read a possible solution as using UNION ALL for example...

(SELECT liked FROM likeTable WHERE uid = uid1 LIMIT 3)
UNION ALL
(SELECT liked FROM likeTable WHERE uid = uid2 LIMIT 3)
UNION ALL
(SELECT liked FROM likeTable WHERE uid = uid3 LIMIT 3)
UNION ALL
(SELECT liked FROM likeTable WHERE uid = uid4 LIMIT 3)

And i would be able to achieve this by making the sql query string in php with a forloop, but is this an efficient way of querying my data?

note: I don't really care about the order in which the "liked" is retrieved, although it would be nice if i could add an ORDER BY likeID DESC, which is my autoincrementing column

Thanks!

Possible duplicate of http://stackoverflow.com/questions/2000744/mysql-limit-results-per-category and http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group — Kemal Fadillah, Sep 09 '11 at 08:12
Try: `SET @cnt := 0; SELECT b.liked FROM likeTable a INNER JOIN likeTable b ON a.uid=b.uid AND (@cnt := @cnt - 1)>0 WHERE uid IN (uid1,uid2,uid3,uid4) AND @cnt := 3 LIMIT 12` — Dor, Sep 09 '11 at 08:24

score 0 · Accepted Answer · answered Sep 09 '11 at 08:14

For small number (not hundreds, I would say) of categories (users), just use the union solution.

And would I be able to achieve this by making the sql query string in php with a forloop, but is this an efficient way of querying my data?

Definitelly yes! Unless you query hundreds of users in one go. For 1-8 people, it is just perfect solution!

How to use MYSQL LIMIT per person

1 Answers1