Hello y'all..

In this query I have two tables, files and file_revisions. I want to select the file record, and join it to the latest file_revisions record for that file (joins using file_id field). For files that don't have a corresponding file_revisions record, I just want to get the file information.

Here is what I've came up with:
SELECT files.*, file_revisions.* FROM files
LEFT JOIN file_revisions
ON (file_revisions.file_id = files.file_id)
GROUP BY files.file_id, file_revisions.file_id
ORDER BY file_revisions.rev_date DESC

I am getting all the files, and the revision information.. but if there are is more than 1 file_revisions record, I seem to be getting the first one in the table (not the most recent).

Thanks for your time
Regards :wave: