Get latest logs query

[lintz]

I've got a log table that records any new files that are downloaded for an app. What I'm trying to do is display a list of user who have downloaded the latest version of the app.

The table looks like the below: Note the DownloadDateTime filed is a
UNIX_TIMESTAMP but the data has been formatted for my example. The latest version of the app is 2.2.

ID UserID FileName FileVersion ComputerName DownloadDateTime
1 1234 myapp.exe 2.1 My-pc 12/12/2010 9:15:00 AM
2 1234 myapp.exe 2.2 gamer_pc 12/12/2010 3:15:00 PM
3 5678 myapp.exe 2.2 computer 11/12/2010 9:15:00 AM
4 5678 myapp.exe 2.2 first_pc 15/12/2010 9:15:00 AM
5 789 myapp.exe 2.2 laptop 9/12/2010 9:15:00 AM
6 789 myapp.exe 2.2 desktop-pc 8/12/2010 9:15:00 AM
7 789 myapp.exe 2.2 desktop-pc 8/12/2010 3:25:00 PM


The query I've currently got is

SELECT ID, UserID, FileName, FileVersion, DownloadDateTime FROM downloadlogs WHERE FileName = 'myapp.exe' AND FileVersion = '2.2' ORDER BY UserID ASC, DownloadDateTime DESC

but it returns every log where the latest version of my app has been downloaded.

The results I'm attempting to get back are:
ID UserID FileName FileVersion ComputerName DownloadDateTime
2 1234 myapp.exe 2.2 gamer_pc 12/12/2010 3:15:00 PM
3 5678 myapp.exe 2.2 computer 11/12/2010 9:15:00 AM
4 5678 myapp.exe 2.2 first_pc 15/12/2010 9:15:00 AM
5 789 myapp.exe 2.2 laptop 9/12/2010 9:15:00 AM
7 789 myapp.exe 2.2 desktop-pc 8/12/2010 3:25:00 PM

[jggtz]

Is FileVersion a char or numeric field?

JG

[lintz]

character

[leinad31]

If you have relevant indexes then you can do a NOT EXISTS correlated query to ensure that the record is the latest for that particular user, app. If you don't have relevant indexes and will have to do full table scans then research use of analytic functions particular to your database. All other approaches will be non-scalable or are less performant.