MySQL: How to limit results to max value of another field?

Question

In this scenario, I am trying to report on the operating_system_version for each distinct computer_id where the report_id for that computer_id is the greatest.

Currently, I am getting the below results:

operating_system_version | computer_id | report_id
10.8 | 1 | 10
10.9 | 1 | 20
10.9 | 2 | 11
10.8 | 2 | 21

The above is returned by this statement:

SELECT operating_systems.operating_system_version,
       reports.computer_id,
       reports.report_id
FROM operating_systems
INNER JOIN reports
    ON operating_systems.report_id = reports.computer_id

Instead, would like return the most recent (highest report_id) operating_system_version for each distinct computer_id, for example:

operating_system_version | computer_id | report_id
10.9 | 1 | 20
10.8 | 2 | 21

I am brand new to SQL .. Appreciate any help.

Answer 1

You would need to add a group by statement and a having statement.

The group by would look like

group by computer_id

The having would look like

having report_id= (select max(report_id) )