MySQL - Find most recent upload for each user

Question

I have the following files table where files are stored for multiple users (the table is only showing files for 1 user).

+--------+----------+----------+------------------------------+
| fileID | fileName | folderID | userID | uploadDate          |        
+--------+----------+----------+--------+---------------------+
| 1      | file1    | 1        | 1      | 2014-02-03 12:00:00 |
| 2      | file2    | 2        | 1      | 2014-01-05 12:00:00 |
| 3      | file3    | 3        | 1      | 2014-01-08 12:00:00 |
| 4      | file4    | 2        | 1      | 2014-01-04 12:00:00 |
| 5      | file5    | 1        | 1      | 2014-01-07 12:00:00 |
| 6      | file6    | 3        | 1      | 2014-01-09 12:00:00 |
+--------+----------+----------+--------+---------------------+

I want to find the most recent file uploaded for each folder by date. The outcome should be like this...

+--------+----------+----------+------------------------------+
| fileID | fileName | folderID | userID | uploadDate          |        
+--------+----------+----------+--------+---------------------+
| 2      | file2    | 2        | 1      | 2014-03-05 12:00:00 |
| 5      | file5    | 1        | 1      | 2014-03-07 12:00:00 |
| 6      | file6    | 3        | 1      | 2014-03-09 12:00:00 |
+--------+----------+----------+--------+---------------------+

Any suggestions?

There's a whole chapter in the MySQL manual dedicated to explaining this problem. And it's been asked and answered several times here. — symcbean, Mar 13 '14 at 23:33
possible duplicate of [Show only most recent date from joined MySQL table](http://stackoverflow.com/questions/1368331/show-only-most-recent-date-from-joined-mysql-table) — symcbean, Mar 13 '14 at 23:36
This a common question http://stackoverflow.com/questions/2657482/sql-find-the-max-record-per-group You should bookmark and stuff the following which has many very common and also very useful queries. Good luck! http://www.artfulsoftware.com/infotree/queries.php — Victory, Mar 13 '14 at 23:33

score 0 · Answer 1 · answered Mar 13 '14 at 23:46

Ah, this is a surprisingly difficult thing to do with SQL. My best solution for this is to create a temporary table and then join against it:

CREATE TABLE Table1 (fileID int, fileName varchar(5), folderID int, userID int, uploadDate datetime) ;

INSERT INTO Table1
    (`fileID`, `fileName`, `folderID`, `userID`, `uploadDate`)
VALUES
    (1, 'file1', 1, 1, '2014-02-03 12:00:00'),
    (2, 'file2', 2, 1, '2014-01-05 12:00:00'),
    (3, 'file3', 3, 1, '2014-01-08 12:00:00'),
    (4, 'file4', 2, 1, '2014-01-04 12:00:00'),
    (5, 'file5', 1, 1, '2014-01-07 12:00:00'),
    (6, 'file6', 3, 1, '2014-01-09 12:00:00')
;

create temporary table last_files (
  fileID int, fileName varchar(5), folderID int, userID int , 
  unique key( folderID, userID )
);

REPLACE INTO last_files ( fileID, fileName, folderID, userID ) 
select fileID, fileName, folderID, userID 
from Table1 order by uploadDate asc;

select * from last_files;

Instead of a subquery, it uses a temporary table to collate the results and then joins back to the original table. It's a pattern I've used on massive tables in the past with pretty good results.

If you're looking for a particular user, you can SORT BY uploadDate DESC LIMIT 1 instead, but that is obviously limited.

MySQL - Find most recent upload for each user

1 Answers1