MySQL: retrieving the last record from each group and combining with the data of another table

There is a purchase table that information regarding the products bought. This will generate one one or more rows in the tStockMP table for each individual product bought.

Now, I need to display the table information for each product in stock. Since the purchase table contains the history of the changes, that information is in the highest keyid when grouped by purchase_id in the tPurchases table.

I’ve provided a complete script, here with example data describing my problem.

DROP TABLE IF EXISTS tPurchases;
DROP TABLE IF EXISTS tStockMP;

-- The purchase table
CREATE TABLE tPurchases (
                            keyid                        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                            brand                        VARCHAR(255),
                            model                        VARCHAR(255),
                            purchase_id                  INT
                           );
                           
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("Hp","note1",23);
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("Lg","IPSLED",45);
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("Hp","notE1",23);
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("Bx","BOX",56);
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("LG","IPSLED",45);
INSERT INTO tPurchases (brand,model,purchase_id) VALUES ("HP","NOTE1",23);

                           
-- The Stock MP Table
CREATE TABLE tStockMP (
                       keyid                        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                       purchase_id                  INT,
                       status                       TINYINT
);

INSERT INTO tStockMP (purchase_id,status) VALUES (23,1);
INSERT INTO tStockMP (purchase_id,status) VALUES (23,1);
INSERT INTO tStockMP (purchase_id,status) VALUES (23,0);
INSERT INTO tStockMP (purchase_id,status) VALUES (45,0);
INSERT INTO tStockMP (purchase_id,status) VALUES (56,1);
INSERT INTO tStockMP (purchase_id,status) VALUES (56,1);
INSERT INTO tStockMP (purchase_id,status) VALUES (56,0);

-- Example data
-- 
-- tPurchases table
-- keyid brand model   purchase_id
-- 0     Hp    note1   23
-- 1     Lg    IPSLED  45
-- 2     Hp    notE1   23
-- 3     Bx    BOX     56
-- 4     LG    IPSLED  45
-- 5     HP    NOTE1   23
-- 
-- 
-- tStockMP   table.
-- purchase_id  status
-- 23           1 
-- 23           1
-- 23           0
-- 45           0
-- 56           1
-- 56           1
-- 56           0
-- 
-- 
-- Expected result
-- 
-- purchase_id  status  brand  model  
-- 23           1       HP     NOTE1   
-- 23           1       HP     NOTE1
-- 23           0       HP     NOTE1
-- 45           0       LG     IPSLED
-- 56           1       Bx     BOX
-- 56           1       Bx     BOX
-- 56           0       Bx     BOX


SELECT s.keyid, s.purchase_id, s.status, p.brand, p.model, p.keyid AS purkeyid 
FROM tStockMP AS s, tPurchases AS p 
WHERE s.purchase_id = p.purchase_id;

-- +-------+-------------+--------+-------+--------+----------+
-- | keyid | purchase_id | status | brand | model  | purkeyid |
-- +-------+-------------+--------+-------+--------+----------+
-- |     1 |          23 |      1 | Hp    | note1  |        1 |
-- |     1 |          23 |      1 | Hp    | notE1  |        3 |
-- |     1 |          23 |      1 | HP    | NOTE1  |        6 |-> *
-- |     2 |          23 |      1 | Hp    | note1  |        1 |
-- |     2 |          23 |      1 | Hp    | notE1  |        3 |
-- |     2 |          23 |      1 | HP    | NOTE1  |        6 |-> *
-- |     3 |          23 |      0 | Hp    | note1  |        1 |
-- |     3 |          23 |      0 | Hp    | notE1  |        3 |
-- |     3 |          23 |      0 | HP    | NOTE1  |        6 |-> *
-- |     4 |          45 |      0 | Lg    | IPSLED |        2 |
-- |     4 |          45 |      0 | LG    | IPSLED |        5 |-> *
-- |     5 |          56 |      1 | Bx    | BOX    |        4 |-> *
-- |     6 |          56 |      1 | Bx    | BOX    |        4 |-> *
-- |     7 |          56 |      0 | Bx    | BOX    |        4 |-> *
-- +-------+-------------+--------+-------+--------+----------+

The I would need to “filter” the results so that ONLY the * rows remain in the final query, lest I need to do it by hand. But I don’t know how to modify my query and make this happen.

Answer

Never use commas in the FROM clause. I typical solution is to use a correlated subquery:

SELECT s.keyid, s.purchase_id, s.status, p.brand, p.model, p.keyid AS purkeyid 
FROM tStockMP s JOIN
     tPurchases p 
     ON s.purchase_id = p.purchase_id
WHERE p.keyid = (SELECT MAX(p2.keyid)
                 FROM tPurchases p2
                 WHERE p2.purchase_id = p.purchase_id
                );

With an index on tPurchases(purchase_id, keyid), this often has the best performance.

If I approached this with window functions, I would phrase it as:

SELECT s.keyid, s.purchase_id, s.status, p.brand, p.model, p.keyid AS purkeyid 
FROM tStockMP s JOIN
     (SELECT p.*,
             ROW_NUMBER() OVER (PARTITION BY purchase_id ORDER BY keyid DESC) as seqnum
      FROM tPurchases p 
     ) p
     ON s.purchase_id = p.purchase_id
WHERE seqnum = 1;

GMB has an alternative approach. If you have lots of data, it would be interesting to compare the performance of the two methods. I would advise the same index as above for all comparisons.