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;
