How to select maximum 3 items per users in MySQL?

Question

Hi,

I run a website where users can post items (e.g. pictures). The items are stored in a MySQL database.

I want to query for the last ten posted items BUT with the constraint of a maximum of 3 items can come from any single user.

What is the best way of doing it? My preferred solution is a constraint that is put on the SQL query requesting the last ten items. But ideas on how to set up the database design is very welcome.

Thanks in advance!

BR

Answer 1

This is difficult because MySQL does not support the LIMIT clause on sub-queries. If it did, this would be rather trivial... But alas, here is my naïve approach:

SELECT
  i.UserId,
  i.ImageId
FROM
  UserSuppliedImages i
WHERE
  /* second valid ImageId */
  ImageId = (
    SELECT MAX(ImageId)
    FROM UserSuppliedImages
    WHERE UserId = i.UserId
  )
  OR
  /* second valid ImageId */
  ImageId = (
    SELECT MAX(ImageId)
    FROM   UserSuppliedImages
    WHERE UserId = i.UserId
      AND ImageId < (
        SELECT MAX(ImageId)
        FROM UserSuppliedImages
        WHERE UserId = i.UserId
      )
    )
  /* you get the picture... 
     the more "per user" images you want, the more complex this will get */
LIMIT 10;

You did not comment on having a preferred result order, so this selects the latest images (assuming ImageId is an ascending auto-incrementing value).

For comparison, on SQL Server the same would look like this:

SELECT TOP 10
  img.ImageId,
  img.ImagePath,
  img.UserId
FROM
  UserSuppliedImages img
WHERE
  ImageId IN (
    SELECT TOP 3 ImageId
    FROM UserSuppliedImages 
    WHERE UserId = img.UserId
  )

Answer 2

I would first select 10 distinct users, then selecting images from each of those users with a LIMIT 3, possibly by a union of all those and limit that to 10.

That would atleast narrow down the data you need to process to a fair amount.

Answer 3

It's pretty easy with a correlated sub-query:

SELECT `img`.`id` , `img`.`userid`
FROM `img`
WHERE 3 > (
SELECT count( * )
FROM `img` AS `img1`
WHERE `img`.`userid` = `img1`.`userid`
AND `img`.`id` > `img1`.`id` )
ORDER BY `img`.`id` DESC
LIMIT 10

The query assumes that larger id means added later

Correlated sub-queries are a powerful tool! :-)