MySQL - DISTINCT on single column
Posted on 2011-10-01
I am having a problem applying a DISTINCT attribute to a single column. Here is my scenarion.
We have a MySQL table used to manage the published files for a given software application
FirmID represents who the file is published to. 0 means everyone.
I need to return the FileID for the files with the highest Version numbers & dates.
SELECT DISTINCT FileName, FileID
WHERE Firmid=1 OR FirmID=0
ORDER BY VersionMajor DESC, VersionMinorDESC, VersionDateDESC
Unfortunately, this applies the DISTINCT attribute to both columns.
Using a GROUP BY returns the right number of results, but the row data is arbitrary (so the FileID and other fields will not be correct)
SELECT FileID, FileName, FileCr32, VersionMajor, VersionMinor, VersionDate
WHERE FirmID=1 OR FirmID=0
GROUP BY FileName
ORDER BY VersionMajor DESC, VersionMinor DESC, VersionDate DESC
Please help me. I, essentially, just need the Distinct to be applied to FileName but the ORDER BY statement must be honored in order to assure I get the correct results.
Thank you in advance