MySQL select “MAX” row from “GROUP BY” result set

Let’s say you have a database containing orders, which map to shipments via a shipment map.  You want to find the most recently created shipment for every product in the database and determine if it was for a quantity > 1. 

So you do a “GROUP BY product_id”, and you then do a “MAX(created_date)” to find the newest entry.  So far: so good.  But now what?  If you simply try and grab the “quantity” field along with the “MAX(created_date)”, those two values will not come from the same row of the table, and you will not get what you are looking for.

The trick is to use the results of this query to create a new table containing just the “product_id” and “created_date” and then match that table against the original table to find the row with the same “product_id” and “created_date” from which we can take “quantity” and anything else we want.  In the example, I just wanted the product_id’s for which the last shipment had a quantity greater than one.  So that’s the only value I picked off.

SELECT ol.product_id FROM order_line ol

INNER JOIN order_line_shipment_map olsm using (order_line_id)

INNER JOIN

    (

        SELECT ol.product_id, max(olsm.created_date) AS max_created_date

         FROM order_line_shipment_map olsm

        INNER JOIN order_line ol USING (order_line_id)

         GROUP BY ol.product_id

    )    AS rtab

ON rtab.vendor_product_id = ol.vendor_product_id

AND olsm.created_date = rtab.max_created_date

WHERE ol.quantity > 1;