'get value with highest ID after using mysql group
I have a website that allows users to upload images for auditing purposes. The user first creates an audit, which has a random number for the auditID and a dateStart (a date the user chooses which is stored as a DATE rather than a DATETIME) stored in the audit table. They then select an image to upload which is then assigned an ID (imageID) and it's file path on the server (imageLocation) and a type which is always 'standard' which is stored in the image table. auditID is a foreign key in the image table.
If the same image is reused for another audit, another entry in the database is made for it which has the same values as before, just a new imageID, and the new auditID which has a new dateStart.
These images are then displayed by using a script that groups the images by it's imageLocation so one instance of each image is displayed along with the the name of the last audit it was used for by using MAX(dateStart).
This works well unless the user uses the same image for multiple audits with the same dateStart in which case it is displayed multiple times, once for each audit on the same date. The easiest thing to do would be to change the dateStart from date to datetime but that isn't a viable option so the next best thing would be to not only find the MAX(dateStart) but also MAX(imageID).
I have tried adding SORT BY i.imageID LIMIT 1 DESC after the GROUP but then it only returns one image rather than the latest instance of each image.
This is what I have so far...
SELECT i.imageID AS newest_image,
i.userID AS image_userID,
i.auditID AS image_auditID,
i.type AS image_type,
i.imageLocation AS location,
a.*
FROM image i
JOIN audit a
ON i.auditID = a.auditID
JOIN
(
SELECT i.imageLocation, MAX(a.dateStart) MaxDate
FROM image i
JOIN audit a
ON i.auditID = a.auditID
WHERE i.userID = '$id'
AND i.type = 'standard'
GROUP BY i.imageLocation
)
g ON g.MaxDate = a.dateStart
AND g.imageLocation = i.imageLocation
WHERE i.userID = '$id'
AND i.type = 'standard'
AND i.imageLocation NOT LIKE '%standard_examples%'"
I would like it to return the same results it does now but if the same image is used more than once on the same date, then I want the instance of the image with the highest imageID.
I hope this is enough information, the tables are quite complicated but I can provide them and sample data if need be but it will take a while.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
