Find the most recent shipment for a product (sql subselect?)

Question

I have three tables

shipment (shipment_id, shipping_date)
company_order (company_order_id, shipment_id, company_id)
company_order_item (company_order_item_id, company_order_id, product_id)

Several companies get together and aggregate orders from a single manufacturer. This aggregate order is called a "shipment". Companies order a selection of products in each shipment: so not all products will be present in any one shipment or for any one company.

How do I write an SQL query find the most recent shipment for each product_id ?

I've looked at http://stackoverflow.com/questions/1792922/sql-query-get-most-recent-revision (much simpler case).

Answer 1

You need to get the max shipment date per product id and then retrieve the shipment detaisl

Something like

SELECT  *
FROM    (
            SELECT  coi.product_id,
                    MAX(s.shipping_date) MaxDate
            FROM    company_order_item coi INNER JOIN
                    company_order co ON coi.company_order_id = co.company_order_id INNER JOIN
                    shipment s ON co.shipment_id =s.shipment_id
            GROUP BY coi.product_id
        ) sub INNER JOIN
        company_order_item coi ON sub.product_id = coi.product_id INNER JOIN
        company_order co ON coi.company_order_id = co.company_order_id INNER JOIN
        shipment s ON   co.shipment_id = s.shipment_id
                    AND s.shipping_date = sub.MaxDate

Answer 2

SQL Code to illustrate - (This is T-SQL and is SQL Server friendly, but i didn't have any mysql handy. The last query should with tiny modifications (to suit your table names) work nicely in MySQL as well.

My logic is to find the most recent company_order for each product_id. Once i have that i can just join the company_order_id to company_order, and i have the shipment_id for each most-recent company_order per product_id

DROP TABLE #shipment
DROP TABLE #company_order
DROP TABLE #company_order_item
CREATE TABLE #shipment
    (
      shipment_id INT ,
      shipping_date INT
    ) ;
CREATE TABLE #company_order
    (
      company_order_id INT ,
      shipment_id INT ,
      company_id INT
    ) ;
CREATE TABLE #company_order_item
    (
      company_order_item_id INT ,
      company_order_id INT ,
      product_id INT
    ) ;

INSERT  INTO #shipment
        ( shipment_id , shipping_date )
VALUES
        ( 1 , 1 ),
        ( 2 , 2 ),
        ( 3 , 3 )

INSERT  INTO #company_order
        ( company_order_id , shipment_id , company_id )
VALUES
        ( 1 , 1 , 1 ),
        ( 2 , 2 , 1 ),
        ( 3 , 3 , 1 )

INSERT  INTO #company_order_item
        ( company_order_item_id , company_order_id , product_id )
VALUES
        ( 1 , 1 , 1 )        ,
        ( 2 , 1 , 2 ),
        ( 2 , 2 , 2 ),
        ( 1 , 1 , 3 ),
        ( 1 , 3 , 4 )

SELECT
    product_id ,
    shipment_id
FROM
    (
      SELECT
        product_id ,
        MAX(company_order_id) AS company_order_id
      FROM
        #company_order_item
      GROUP BY
        product_id
    ) AS MostRecentProductInOrder
INNER JOIN #company_order
ON  MostRecentProductInOrder.company_order_id = #company_order.company_order_id