multiple rows with the same maximum value

Question

When I run the following query,

SELECT engines, manufacturer, model, MAX(seats)
FROM   planes
GROUP BY engines;

I am getting the correct engines and seats results but not the correct manufacturer, model combination. In addition, there are multiple rows with the same maximum value for seats number which I need but just getting one result by engines/seats. I have viewed other Stack Exchange posts and elsewhere but cannot seem to find a good solution to fix the query. Any advice?

Answer 1

Yours appears to be a "greatest N per group" problem. What you can do is get the maximum seats per engine results:

SELECT
  engines,
  MAX(seats) AS max_seats
FROM
  planes
GROUP BY
  engines

and, using them as a derived table, join them back to the source to get the rows matching the maximums:

SELECT
  p.engines,
  p.manufacturer,
  p.model,
  es.max_seats
FROM
  planes AS p
  INNER JOIN
  (
    SELECT
      engines,
      MAX(seats) AS max_seats
    FROM
      planes
    GROUP BY
      engines
  ) AS es
  ON p.engines = es.engines
  AND p.seats = es.max_seats
;

Answer 2

The problem is that you have more columns in the select than in the group by. This is allowed with default settings in MySQL, but may lead to in-deterministic results. Example:

engines, manufacturer, model, seats
A        A            A       10
A        B            C       20

Your query:

SELECT engines, manufacturer, model, MAX(seats)
FROM   planes
GROUP BY engines;

then means, "get the max number of seats for engine 'A' and randomly pick a manufacturer/model for 'A'. This could mean:

+---------+--------------+-------+------------+
| engines | manufacturer | model | MAX(seats) |
+---------+--------------+-------+------------+
| A       | A            | B     |         20 |
+---------+--------------+-------+------------+

but could just as well have been:

+---------+--------------+-------+------------+
| engines | manufacturer | model | MAX(seats) |
+---------+--------------+-------+------------+
| A       | B            | C     |         20 |
+---------+--------------+-------+------------+

You can prevent this by requiring a full group by:

MariaDB [test]> set @@sql_mode = 'ONLY_FULL_GROUP_BY'
-> ;
Query OK, 0 rows affected (0,01 sec)

MariaDB [test]> SELECT engines, manufacturer, model,  MAX(seats)     FROM   planes     GROUP BY engines;
ERROR 1055 (42000): 'test.planes.manufacturer' isn't in GROUP BY

If you want the manufacturer/model for the engine with most seats you can first get the max seats for the engine, and then join the original table as in @Andriy M answer:

SELECT A.engines, B.manufacturer, B.model, A.seats 
from (
    SELECT engines, MAX(seats) as seats    
    FROM   planes     
    GROUP BY engines
) as A 
join planes B 
    on A.engines = B.engines 
   and A.seats = B.seats;

+---------+--------------+-------+-------+
| engines | manufacturer | model | seats |
+---------+--------------+-------+-------+
| A       | B            | C     |    20 |
+---------+--------------+-------+-------+

Other DBMS such as PostgreSQL, Oracle, DB2 and SQL-Server provides window functions that makes this kind of query easier:

select engines, manufacturer, model, seats
from (
    select engines, manufacturer, model, seats
         , row_number() over (partition by engine
                              order by seats desc) as rn
    from planes
) where rn = 1;

Answer 3

I think the problem is that you have columns in the select that are not grouped by and not aggregated.

I am not sure what you are trying to do exactly but if you want one row for every Engine/Manufacturer/Model with the max seats in that grouping, you should add them to the group by.