TommyDo TommyDo - 11 days ago 5
MySQL Question

SQL - How to get newest data of same date created

I have table images with uploaded date field. How can I get the newest image if 2 or more images were uploaded in same day.

1.We check on hours, minutes or seconds.

2.If the H:M:S are same, check on the sequences of inserted ID.

ID | URL | create |
1 | 01.jpg | 2017-02-23 10:24:41 |<<same H:M:s
2 | 02.jpg | 2017-02-23 10:24:41 |<<same H:M:s
3 | 03.jpg | 2017-02-23 10:50:00 |<<same H
4 | 04.jpg | 2017-02-24 21:50:00 |<<others
5 | 05.jpg | 2017-03-28 17:50:00 |<<others


Output: I want to get only

3 | 03.jpg | 2017-02-23 10:50:00 |<< newer than 1, 2
4 | 04.jpg | 2017-02-24 21:50:00 |
5 | 05.jpg | 2017-03-28 17:50:00 |

Answer
SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT MAX(create) AS latest, MAX(ID) AS maxID
    FROM yourTable
    GROUP BY DATE(create)
) t2
    ON t1.create = t2.latest AND
       t1.ID     = t2.maxID
Comments