How to get the greatest element of a GROUP BY?

As you can see I would like to get the latest purchase order for each SKU.

Sample Data

I have this data set,

CREATE TABLE PurchaseOrders (
        id            int  PRIMARY KEY AUTO_INCREMENT,
        sku           varchar(6),
        purchase_date date
);

INSERT INTO PurchaseOrders VALUES
    ( 1, 'ABC123', '2017-12-23' ),
    ( 2, 'ABC123', '2016-11-11' ),
    ( 3, 'DEF456', '2011-01-03' ),
    ( 4, 'DEF456', '2011-10-21' ),
    ( 5, 'GHI789', '2017-01-23' ),
    ( 6, 'GHI789', '2017-11-21' );

Desired Result

1 | ABC123 | 2017-12-23
4 | DEF456 | 2011-10-21
6 | GHI789 | 2017-11-21

Probably easiest to do with a join against a derived table:

select a.* 
from PurchaseOrders as a 
join ( 
    select sku, max(date) as date
    from PurchaseOrders
    group by sku
) as b 
   on a.sku = b.sku 
   and a.date = b.date;

The derived table b contains the max dt for each sku. To get the id we join that against the original table.

As Evan Carroll points out you can shorten this a bit since the name of the join attributes is the same for both left and right operand:

select a.* 
from PurchaseOrders as a 
join ( 
    select sku, max(date) as date
    from PurchaseOrders
    group by sku
) as b 
   using (sku, date);

StackExchange Database Administrators Q#202968, answer score: 7