How to get the greatest element of a GROUP BY?

Question

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

Answer 1

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);

Answer 2

Although not quite yet stable (GA), MySQL 8.0+ (and MariaDB 10.2 - stable (GA)) supports CTEs and window functions, so we can do:

WITH ranked AS (
  SELECT id, sku, purchase_date, RANK() OVER (
    PARTITION BY sku ORDER BY purchase_date DESC
  ) AS rnk 
  FROM PurchaseOrders
) 
SELECT r.id, r.sku, r.purchase_date 
FROM ranked r 
WHERE r.rnk=1
ORDER BY r.id;

I don't know if this is likely, but if we imagine an additional record, essentially a duplicate of the data in record with id 6:

INSERT INTO PurchaseOrders (id, sku, purchase_date) VALUES (7, 'GHI789', '2017-11-21');

... then this new record will be displayed as well. If you want to avoid that, we can use ROW_NUMBER instead of RANK().

With the CTE + window function approach it's also trivial to get, let's say, the second most recent purchase order per sku, or the two most recent ones, and so forth, by modifying the WHERE r.rnk=1 clause.

Answer 3

Let's break your problem into two part one would be getting the latest orders for each group that can very much be found by the following query:

select sku,max([date]) as OrderDate from PurchaseOrders
group by sku

It will yield the following results:

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

Next what you want is the id for these orders that can be done using the following query:

select * from PurchaseOrders a,(
    select sku,max([date]) as OrderDate from PurchaseOrders
    group by sku

) as b
where a.sku in(b.sku) and a.[date] in (b.OrderDate)
order by id

This will yield your final result as:

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

Hope this is the answer you are looking for :).

Answer 4

I hope it will help you.

SELECT max(date) AS date, sku
FROM PurchaseOrders
GROUP BY sku;