Correlate GROUP BY and LEFT JOIN on multiple criteria to show latest record?

Question

In a simple stock management database, quantity of new stock is added and shipped until quantity reaches zero. Each stock movement is assigned a reference, only the latest reference is used.

In the example provided, the latest references are never shown, the stock ID's 1,4 should have references charlie, foxtrot respectively, but instead show alpha, delta.

How can a GROUP BY and LEFT JOIN on multiple criteria be correlated to show the latest record?

http://sqlfiddle.com/#!2/6bf37/107

CREATE TABLE stock (
  id tinyint PRIMARY KEY,
  quantity int,
  parent_id tinyint
);

CREATE TABLE stock_reference (
  id tinyint PRIMARY KEY,
  stock_id tinyint,
  stock_reference_type_id tinyint,
  reference varchar(50)
);

CREATE TABLE stock_reference_type (
  id tinyint PRIMARY KEY,
  name varchar(50)
);

INSERT INTO stock VALUES 
(1, 10, 1),
(2, -5, 1),
(3, -5, 1),
(4, 20, 4),
(5, -10, 4),
(6, -5, 4);

INSERT INTO stock_reference VALUES 
(1, 1, 1, 'Alpha'),
(2, 2, 1, 'Beta'),
(3, 3, 1, 'Charlie'),
(4, 4, 1, 'Delta'),
(5, 5, 1, 'Echo'),
(6, 6, 1, 'Foxtrot');

INSERT INTO stock_reference_type VALUES 
(1, 'Customer Reference');

SELECT stock.id, SUM(stock.quantity) as quantity, customer.reference
FROM stock
LEFT JOIN stock_reference AS customer ON stock.id = customer.stock_id AND stock_reference_type_id = 1
GROUP BY stock.parent_id

Answer 1

You can use a subquery to pull the latest ID for each stock group:

SELECT g.parent_id, g.quantity, customer.reference
FROM (
    SELECT parent_id, SUM(stock.quantity) as quantity, MAX(id) as LatestID
    FROM stock
    GROUP BY parent_id
) g LEFT JOIN stock_reference AS custome
    ON g.LatestID = customer.stock_id AND stock_reference_type_id = 1