Getting the latest entry for a distinct column value

Question

I have 2 tables I need to reference software_releases and platforms. The software releases will contain a platform id, software id and version number.

I want to get the latest version number for each of the distinct platforms.

The query that I currently have is the following:

SELECT   ReleaseID, 
         DateCreated, 
         SoftwareID, 
         platforms.PlatformID, 
         PlatformName, 
         VersionNumber 
FROM     software_releases, platforms 
WHERE    software_releases.PlatformID = platforms.PlatformID 
AND      software_releases.SoftwareID='3'  
ORDER BY VersionNumber DESC

Which returns:

5   27/05/2017 22:37    3   7   Windows 3.0.0.0
9   27/05/2017 22:56    3   7   Windows 2.6.0.0
7   27/05/2017 22:46    3   5   Android 2.5.1.1
1   27/05/2017 23:21    3   5   Android 2.5.0.0

The column order is as follows:

ReleaseID
Date Released
Software ID
Platform ID
Version ID

What I am looking for is getting the latest release for each platform returned for the specified software ID, therefore, I am only wanting to return the following:

5   27/05/2017 22:37    3   7   Windows 3.0.0.0
7   27/05/2017 22:46    3   5   Android 2.5.1.1

@CBroe thanks, no idea that's what it was called - was a tricky thing to Google without that — Boardy, May 27 '17 at 22:48

score 0 · Answer 1 · answered May 27 '17 at 22:47

I got this answer from CBrowe comment, I had no idea what I was after was called a group wise maximum query.

I changed my query to be

         SELECT * FROM (SELECT DateCreated, SoftwareID, software_releases.PlatformID,
         VersionNumber, PlatformName FROM software_releases, platforms WHERE 
         SoftwareID='3' AND 
         platforms.PlatformID = software_releases.PlatformID ORDER BY VersionNumber DESC) 
         AS s GROUP BY PlatformID

Getting the latest entry for a distinct column value

1 Answers1