CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
user1890913
user1890913

Reputation: 107

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

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.

Upvotes: 2

Views: 502

Answers (4)

user1890913
user1890913

Reputation: 107

Need to do a better job looking through other posts. This question is answered in an excellent post at: SQL Select only rows with Max Value on a Column

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

A subquery would lead you to desired end result:

SELECT os.operating_system_version,
       r2.computer_id,
       MAX(r2.report_id)
FROM (
    SELECT DISTINCT computer_id
    FROM reports
) r
INNER JOIN operating_systems os
    ON os.report_id = r.computer_id
INNER JOIN reports r2
    ON r2.computer_id = r.computer_id

Upvotes: 0

Dijkgraaf
Dijkgraaf

Reputation: 11527

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
WHERE NOT EXISTS (SELECT 1 
                    FROM  reports r2 
                    WHERE r2.computer_id = reports.computer_id 
                    AND r2.reports_id > reports.reports_id)

Upvotes: 0

user2843336
user2843336

Reputation: 44

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) )

Upvotes: 1

Related Questions