2

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?

5

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
;
  • The join reconciles the correct info as @Lennart's query did too... – G Mode Sep 5 '16 at 14:03
  • @GMode: His answer also does a really good job of explaining what was wrong with your original query – much superior to the one you chose to accept, if you pardon my forwardness. – Andriy M Sep 5 '16 at 14:15
  • @Andriy..pardoned. All responses/queries did really good jobs to increase my understanding of where I went awry. Also pardon my newness to the stackexchange.com community. – G Mode Sep 5 '16 at 16:02
4

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;
  • 1
    +1. The indeterminate result could even be from multiple rows (in some rare cases), eg. : A B A 20 ... ! – ypercubeᵀᴹ Sep 5 '16 at 8:34
  • The join reconciles the correct info. – G Mode Sep 5 '16 at 12:59
0

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.

  • Amir..my task is..What are the diff #s of engines in the planes table? For each # of engines, which aircraft have the most # of seats? – G Mode Sep 4 '16 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.