Nested aggregation and grouping on multiple columns in MySQL Consider the following product table (which is highly trimmed down):

`id` int AUTO_INCREMENT
`category_id` int
`subcategory_id` int
`vendor_id` int
`price` decimal(6,2)
`inserted_at` timestamp


For a given category ID, I am attempting to retrieve a list containing the vendor with the lowest latest price for each subcategory. With "latest" I mean that vendors may have multiple prices for a given category ID/subcategory ID combination, so only the most recently inserted price for that category ID/subcategory ID/vendor ID should be used. If there's a tie between 2 or more vendor's prices, the lowest id should be used as the tie-breaker.

For example, with this data:

id | category_id | subcategory_id | vendor_id | price | inserted_at
---------------------------------------------------------------------------
1  | 1           | 2              | 3         | 16.00 | 2015-07-23 04:00:00  
2  | 1           | 1              | 2         |  9.00 | 2015-07-26 08:00:00 
3  | 1           | 2              | 4         | 16.00 | 2015-08-02 10:00:00 
4  | 1           | 1              | 1         |  7.00 | 2015-08-04 11:00:00
5  | 1           | 1              | 1         | 11.00 | 2015-08-09 16:00:00


So, first find the most recent prices for every subcategory/vendor combination (row with price=7.00 would be removed because it's not the most recent for that vendor in that subcategory). Then for subcategory 1 the lowest price would be 9 (so vendor_id = 2) and for subcategory 2 the lowest price is 16 (two vendors tie ()ids 3 and 4) so we choose the one with lowest vendor_id = 3).

I would expect the following results for category_id = 1:

subcategory_id | vendor_id | price
----------------------------------
1              | 2         |  9.00
2              | 3         | 16.00


Here's what I have so far. I feel like it's already starting to get out of hand and this doesn't even account for ties between 2 or more vendor's prices.

SELECT c.subcategory_id, c.vendor_id, c.price
FROM products AS c
JOIN
(
    SELECT MIN(a.price) AS min_price,
           a.subcategory_id
    FROM products AS a
    JOIN
    (
        SELECT  MAX(`inserted_at`) AS latest_price_time,
                vendor_id,
                subcategory_id
        FROM    products
        WHERE   category_id = 1
        GROUP BY vendor_id, subcategory_id
    ) AS b
    ON a.inserted_at = b.latest_price_time AND a.vendor_id = b.vendor_id AND a.subcategory_id = b.subcategory_id
    WHERE a.category_id = 1
    GROUP BY a.subcategory_id
) AS d
ON c.price = d.min_price AND c.subcategory_id = d.subcategory_id
WHERE c.category_id = 1


Before I go any further, I wanted to see if there was an easier way. When it comes to grouping/aggregating results of additional groupings/aggregations, is there a method that will give me the best performance (most important) and/or be easier to read (less important)?
This should work:

SELECT
    d.subcategory_id,
    d.vendor_id,
    MIN(d.price) AS price,
    d.inserted_at
FROM product AS d
JOIN (SELECT
        b.category_id,
        b.subcategory_id,
        b.vendor_id,
        a.last_iat
    FROM product AS b 
    JOIN (SELECT
            a.category_id,
            a.subcategory_id,
            a.vendor_id,
            a.price,
            MAX(a.inserted_at) AS last_iat
        FROM product AS a
        GROUP BY a.category_id,a.subcategory_id,a.vendor_id
        ) AS a
        ON (a.category_id=b.category_id AND a.subcategory_id=b.subcategory_id AND a.vendor_id=b.vendor_id)
    GROUP BY b.category_id,b.subcategory_id,b.vendor_id) AS c
    ON (c.category_id=d.category_id AND c.subcategory_id=d.subcategory_id AND c.last_iat=d.inserted_at)
WHERE d.category_id=1
GROUP BY d.category_id,d.subcategory_id;


Test:

mysql&gt; SELECT
    -&gt;     d.subcategory_id,
    -&gt;     d.vendor_id,
    -&gt;     MIN(d.price) AS price,
    -&gt;     d.inserted_at
    -&gt; FROM product AS d
    -&gt; JOIN (SELECT
    -&gt;         b.category_id,
    -&gt;         b.subcategory_id,
    -&gt;         b.vendor_id,
    -&gt;         a.last_iat
    -&gt;     FROM product AS b 
    -&gt;     JOIN (SELECT
    -&gt;             a.category_id,
    -&gt;             a.subcategory_id,
    -&gt;             a.vendor_id,
    -&gt;             a.price,
    -&gt;             MAX(a.inserted_at) AS last_iat
    -&gt;         FROM product AS a
    -&gt;         GROUP BY a.category_id,a.subcategory_id,a.vendor_id
    -&gt;         ) AS a
    -&gt;         ON (a.category_id=b.category_id AND a.subcategory_id=b.subcategory_id AND a.vendor_id=b.vendor_id)
    -&gt;     GROUP BY b.category_id,b.subcategory_id,b.vendor_id) AS c
    -&gt;     ON (c.category_id=d.category_id AND c.subcategory_id=d.subcategory_id AND c.last_iat=d.inserted_at)
    -&gt; WHERE d.category_id=1
    -&gt; GROUP BY d.category_id,d.subcategory_id;
+----------------+-----------+-------+---------------------+
| subcategory_id | vendor_id | price | inserted_at         |
+----------------+-----------+-------+---------------------+
|              1 |         2 |  9.00 | 2015-07-26 08:00:00 |
|              2 |         3 | 16.00 | 2015-07-23 04:00:00 |
+----------------+-----------+-------+---------------------+
2 rows in set (0.00 sec)

mysql&gt; 


Explain:

I used the index recommendation from @ypercube.

mysql&gt; EXPLAIN SELECT d.subcategory_id, d.vendor_id, MIN(d.price) AS price, d.inserted_at FROM product AS d JOIN (SELECT b.category_id, b.subcategory_id, b.vendor_id, a.last_iat FROM product AS b  JOIN (SELECT a.category_id, a.subcategory_id, a.vendor_id, a.price, MAX(a.inserted_at) AS last_iat FROM product AS a GROUP BY a.category_id,a.subcategory_id,a.vendor_id ) AS a ON (a.category_id=b.category_id AND a.subcategory_id=b.subcategory_id AND a.vendor_id=b.vendor_id) GROUP BY b.category_id,b.subcategory_id,b.vendor_id) AS c ON (c.category_id=d.category_id AND c.subcategory_id=d.subcategory_id AND c.last_iat=d.inserted_at) WHERE d.category_id=1 GROUP BY d.category_id,d.subcategory_id;
+----+-------------+------------+-------+---------------+------+---------+--------------------------------------------+------+----------------------------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref                                        | rows | Extra                                        |
+----+-------------+------------+-------+---------------+------+---------+--------------------------------------------+------+----------------------------------------------+
|  1 | PRIMARY     | &lt;derived2&gt; | ALL   | NULL          | NULL | NULL    | NULL                                       |    4 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | d          | ALL   | q_ix          | NULL | NULL    | NULL                                       |    5 | Using where; Using join buffer               |
|  2 | DERIVED     | &lt;derived3&gt; | ALL   | NULL          | NULL | NULL    | NULL                                       |    4 | Using temporary; Using filesort              |
|  2 | DERIVED     | b          | ref   | q_ix          | q_ix | 15      | a.category_id,a.subcategory_id,a.vendor_id |    1 | Using where; Using index                     |
|  3 | DERIVED     | a          | index | NULL          | q_ix | 19      | NULL                                       |    5 |                                              |
+----+-------------+------------+-------+---------------+------+---------+--------------------------------------------+------+----------------------------------------------+
5 rows in set (0.00 sec)

mysql&gt;