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

<p>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. </p>

<p>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.</p>

<p>Thanks in advance!</p>

<p>BR</p>

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
  )
```
```