Self Join Issue, I Think?

I have one table(could only have one) that looks like seen below. It contains a plant code, an article number and batches related to that article. plant - code - batch - value - vo

Solution 1:

Here's a possibility using a JOIN subquery which retrieves the volume by the MAX(added_date):

SELECT
  l.code,
  l.plant,
  COUNT(DISTINCT l.batch) AS Batches,
  lvol.vol AS volume
FROM
  lists l JOIN (
    SELECT code, plant, volume AS vol FROM lists GROUP BY code, plant HAVING added_date = MAX(added_date)
  ) lvol ON l.code = lvol.code AND l.plant = lvol.plant AND l.volume = lvol.volume
GROUP BY l.code, l.plant

Solution 2:

SELECT  t.*,
        (SELECT  volume 
           FROM  lists
          WHERE  plant = t.plant
            AND  code  = t.code
          ORDER BY added_date DESC
          LIMIT 1   --Take the latest record for the plant & code
          ) AS volume                  
  FROM  (
            SELECT code, plant,
                COUNT(DISTINCT(batch)) as Batches,
                SUM(value) as TotalValue,
                SUM(value * risk) as TotalRisk
            FROM lists 
            GROUP BY code, plant
        ) AS t