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.

This question is very common. Follow the [tag:greatest-n-per-group] tag for many solutions. — Bill Karwin, Oct 17 '13 at 03:11
Ok wow. Found an excellent post as suggested: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — user1890913, Oct 17 '13 at 03:47

score 1 · Accepted Answer · answered Oct 17 '13 at 02:35

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

answered Oct 17 '13 at 02:35

user2843336

44
4

score 0 · Answer 2 · answered Oct 17 '13 at 02:37

0

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)

answered Oct 17 '13 at 02:37

Dijkgraaf

11,049
17
42
54

Likely because I'm a n00b, but all I get with this is: "Unknown column 'r2.reports_id' in 'where clause'" – user1890913 Oct 17 '13 at 03:39

score 0 · Answer 3 · answered Oct 17 '13 at 02:41

0

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

answered Oct 17 '13 at 02:41

hjpotter92

78,589
36
144
183

Unfortunately this returns only one record: operating_system_version | computer_id | MAX(r2.report_id) 10.8 | 2 | 21 Instead of the needed return of: 10.9 | 1 | 20 10.8 | 2 | 21 which is the highest report_id for each computer_id. – user1890913 Oct 17 '13 at 03:37

score 0 · Answer 4 · edited May 23 '17 at 12:10

0

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

edited May 23 '17 at 12:10

Community

1
1

answered Oct 17 '13 at 03:49

user1890913

107
6

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

4 Answers4