CODEWITHSUNDEEP
phpmysqlsqlselectgreatest-n-per-group
Britt Dietz
Britt Dietz

Reputation: 23

MYSQL Select 5 records for the last 5 distinct records

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

Upvotes: 1

Views: 3304

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

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.

Upvotes: 3

rdans
rdans

Reputation: 2157

Its not a pretty query but something like this should work

select pictures.*
from pictures
inner join
(
  select id
  from pictures
  group by added
  order by added desc
  limit 5
) as galleries
on pictures.added = galleries.added
order by pictures.id desc

where
  (select count(0) from pictures as ip where ip.added = pictures.added and ip.id > pictures.id) >= 5

Upvotes: 1

Exwolf
Exwolf

Reputation: 157

Your select query should look like this:

mysql_query("SELECT * FROM tablename LIMIT 5 ORDER BY ID DESC");

Basically, you select the last 5 results by descending order.

I do recommend you use MySQLi or PDO instead of MySQL

Upvotes: 0

Related Questions