How can I get an even distribution using WHERE id IN(1,2,3,4)

Question

I have a query that is pulling users who liked a specific object from a users table. Ratings are stored in a ratings table. The query I have come up with so far looks like this:

SELECT user.id, user.name, user.image
  FROM users
LEFT JOIN ratings ON ratings.userid = user.id
WHERE rating.rating > 0
  AND rating.objectId IN (1,2,3,4)

I want to be able to put a LIMIT on this query, to avoid returning all the results, when I only need 3 or so results for each ID. If I just put a LIMIT 12 for example, I might get 8 records with one id, and 1 or 2 each for the others - i.e. an uneven distribution across the IDs.

Is there a way to write this query so as to guarantee that (assuming an object has been "liked" at least three times), I get three results for each of the ids in the list?

What do you mean by "first three"? First according to what ordering? — Mark Byers, Jun 27 '12 at 19:45
@MarkByers "first three" is arbitrary in this case. Any three will do for now :) — jches, Jun 27 '12 at 19:50

score 2 · Accepted Answer · answered Jun 27 '12 at 19:54

By setting the row number whit variables, and then filter that result to show only row 1-3 should work

SET @last_objectId = 0;
SET @count_objectId = 0;
SELECT id, name, image FROM (
SELECT
 user.id,
 user.name,
 user.image,
 @count_objectId := IF(@last_objectId = rating.objectId, @count_objectId, 0) + 1 AS rating_row_number,
 @last_objectId := rating.objectId
FROM users
LEFT JOIN ratings ON (ratings.userid = user.id)
WHERE
 rating.rating > 0 AND
 rating.objectId IN (1,2,3,4)
ORDER BY rating.objectId
) AS subquery WHERE rating_row_number <= 3;

this doesn't seem to perform any better than selecting all the ratings for the objects in question, which makes sense since it is selecting and counting the rows as it goes. That being said, is there any way to make it perform even a little better? — jches, Jun 27 '12 at 22:04

How can I get an even distribution using WHERE id IN(1,2,3,4)

1 Answers1

Linked