MYSQL为最近5个不同的记录选择5条记录

Question

I've searched on here for a few different ways to do this, but can't quite get this to work. Basically, I have a table with a record of images added to a website. Each image is put into this table. I want to grab the first 5 images from each distinct Added field.

So, the table may look like this:

ID  File    Folder  Added
----------------------------------
13  13.jpg  Event3  20130830
12  12.jpg  Event3  20130830
11  11.jpg  Event3  20130830
10  10.jpg  Event3  20130830
9   9.jpg   Event3  20130830
8   8.jpg   Event2  20130701
7   7.jpg   Event2  20130701
6   6.jpg   Event2  20130701
5   5.jpg   Event2  20130701
4   4.jpg   Event1  20130615
3   3.jpg   Event1  20130615
2   2.jpg   Event1  20130615
1   1.jpg   Event1  20130615

And I want the return to be like this:

ID  File    Folder  Added
----------------------------------
13  13.jpg  Event3  20130830
12  12.jpg  Event3  20130830
8   8.jpg   Event2  20130701
7   7.jpg   Event2  20130701
4   4.jpg   Event1  20130615
3   3.jpg   Event1  20130615

So basically getting the last 5 (sorted by highest ID first) images for the last 5 distinct 'Added' dates (again sorted for the most recent ones by the Added field). Thank you!

EDIT ---------------

So it's a bit more clear... I have a table full of images I've uploaded to an image based website. For the front of the website, I want to have a news blurb that shows the last 5 galleries that have had images uploaded to them, and display 5 images from each of those galleries. Each image that is in the mysql has an uploaded date ('Added') which corresponds to the gallery since I only upload images to a gallery one day at a time , and ID Number that auto increases with every image added ('ID'). There's a bunch of other fields of course, but those are the most important ones for what I'm trying to do.

EDIT #2 ----------

There's a lot of confusion over how I'm wording this, it's a bit tough to explain but basically how can I get 5 of the highest ID fields for 5 distinct Added date fields from a table?

I believe it has to do with this but it does not work when I run it:

    select TOP 5 * from (
    select *,
 row_no = row_number() over (partition by Added order by ID)
 from AviationImages) d
 where d.row_n <= 5

Answer 1

In many other DBMS (Oracle, SQL-Server, Postgres) you could use window functions:

SELECT id, file, folder, added
FROM
  ( SELECT id, file, folder, added,
           DENSE_RANK() OVER (ORDER BY added DESC) AS d_rank,
           ROW_NUMBER() OVER (PARTITION BY added ORDER BY id DESC) AS row_no
    FROM AviationImages
  ) d
WHERE d_rank <= 5          -- limit number of dates
  AND row_no <= 5 ;        -- limit number of images per date

In MySQL you don't have the luxury of window function and OVER clause:

SELECT i.id, i.file, i.folder, i.added
FROM
    ( SELECT DISTINCT added
      FROM AviationImages
      ORDER BY added DESC
      LIMIT 5
    ) AS da
  JOIN
    AviationImages AS i
      ON  i.added = da.added
      AND i.id >= COALESCE(
          ( SELECT ti.id
            FROM AviationImages AS ti
            WHERE ti.added = da.added
            ORDER BY ti.id DESC
            LIMIT 1 OFFSET 4
          ), -2147483647) ;             -- use 0 if the `id` is unsigned int

An index on (added, id) will help efficiency - and if the table uses InnoDB and the id is the primary key, then just an index on (added) will be enough.