将子查询结果集限制为最后一条记录

Question

I have four tables that are linked. The itemStatus table can have multiple records per Cart item to show its history (e.g. purchased, processed, shipped, delivered). The Shop table pulls in the Cart items name. The Order table defines all the Cart items in the order.

To show the latest status (not entire history) of each Cart item in the Order, I need to only obtain the last record for itemStatus. I am using a sub-query to obtain the records of the itemStatus table, but can not figure out how to get the last record. Playing with the below code, I end up always getting the first record:

SELECT
    tblOrders.id,tblOrders.status as orderStatus,tblOrders.created,tblCart.id AS cartID,tblCart.status AS cartStatus,tblCart.qty,tblCart.price,tblShop.title,tblItem.itemStatus 
FROM
    ".PREFIX."Orders tblOrders 
LEFT JOIN
    ".PREFIX."Cart tblCart ON tblOrders.id=tblCart.OID 
LEFT JOIN
    ".PREFIX."Shop tblShop ON tblCart.PID=tblShop.id 
LEFT JOIN
    (SELECT
        CID,status as itemStatus
    FROM
        ".PREFIX."ItemStatus
    WHERE
        status IN ('pending','refunded','cancelled','purchased','backordered','shipping','delivered')
GROUP BY CID
    ORDER BY
        created DESC
    ) tblItem ON tblCart.id=tblItem.CID 
WHERE
    tblOrders.status<>'disabled' AND tblCart.status='purchased' AND tblCart.BID='".$gbl_user['id']."' 
ORDER BY
    tblOrders.updated DESC

As shown above, I have tried using the 'GROUP BY' clause to limit the number of records returned by the sub-query, but that does not produce desired results. I have also tried placing a 'LIMIT 1' clause in the sub-query too, but that only limits the number of records returned to 1 (so any other cart items do not end up with a status). Any help would be appreciated!

UPDATE:

Per the duplication marking, I attempted to update the code using the MAX() statement and removal of 'ORDER BY' as follows:

SELECT
    tblOrders.id,tblOrders.status as orderStatus,tblOrders.created,tblCart.id AS cartID,tblCart.status AS cartStatus,tblCart.qty,tblCart.price,tblShop.title,tblItem.itemStatus 
FROM
    ".PREFIX."Orders tblOrders 
LEFT JOIN
    ".PREFIX."Cart tblCart ON tblOrders.id=tblCart.OID 
LEFT JOIN
    ".PREFIX."Shop tblShop ON tblCart.PID=tblShop.id 
LEFT JOIN
    (SELECT
        CID,status as itemStatus,MAX(created) as itemCreated
    FROM
        ".PREFIX."ItemStatus
    WHERE
        status IN ('pending','refunded','cancelled','purchased','backordered','shipping','delivered')
GROUP BY CID
    ) tblItem ON tblCart.id=tblItem.CID 
WHERE
    tblOrders.status<>'disabled' AND tblCart.status='purchased' AND tblCart.BID='".$gbl_user['id']."' 
ORDER BY
    tblOrders.updated DESC

UPDATE 2:

I had provided the actual code that works to solve the problem, but it wasn't approved for some reason. I am posting the correct code below:

SELECT
    tblOrders.id, tblOrders.status as orderStatus, tblOrders.created,
    tblCart.id AS cartID, tblCart.status AS cartStatus,tblCart.qty,
    tblCart.price, tblShop.title, tblItem.status as itemStatus 
FROM
    ".PREFIX."Orders tblOrders 
LEFT JOIN
    ".PREFIX."Cart tblCart ON tblOrders.id=tblCart.OID 
LEFT JOIN
    ".PREFIX."Shop tblShop ON tblCart.PID=tblShop.id 
LEFT JOIN
    ".PREFIX."ItemStatus tblItem ON tblCart.id=tblItem.CID 
JOIN (
    SELECT
        CID, MAX(created) AS maxCreated
    FROM
        ".PREFIX."ItemStatus
    WHERE
        status IN ('pending','refunded','cancelled','purchased','backordered','shipping','delivered')
    GROUP BY
        CID
) tblMaxItem ON tblItem.CID=tblMaxItem.CID AND tblItem.created=tblMaxItem.maxCreated
WHERE
    tblOrders.status<>'disabled' AND tblCart.status='purchased' AND tblCart.BID='".$gbl_user['id']."' 
ORDER BY
    tblOrders.updated DESC

Answer 1

You need to join with both ItemStatus and a subquery that gets the latest date for each item, so you can get other columns columns from that row.

SELECT
    tblOrders.id, tblOrders.status as orderStatus, tblOrders.created,
    tblCart.id AS cartID, tblCart.status AS cartStatus,tblCart.qty,
    tblCart.price, tblShop.title, tblItem.status AS itemStatus
FROM
    ".PREFIX."Orders tblOrders 
LEFT JOIN
    ".PREFIX."Cart tblCart ON tblOrders.id=tblCart.OID 
LEFT JOIN
    ".PREFIX."Shop tblShop ON tblCart.PID=tblShop.id 
LEFT JOIN
    ".PREFIX."ItemStatus tblItem ON tblCart.id=tblItem.CID 
LEFT JOIN (
    SELECT
        CID, MAX(created) AS maxCreated
    FROM
        ".PREFIX."ItemStatus
    WHERE
        status IN ('pending','refunded','cancelled','purchased','backordered','shipping','delivered')
    GROUP BY
        CID
) tblMaxItem ON tblItem.CID=tblMaxItem.CID AND tblItem.created=tblMaxItem.maxCreated
WHERE
    tblOrders.status<>'disabled' AND tblCart.status='purchased' AND tblCart.BID='".$gbl_user['id']."' 
ORDER BY
    tblOrders.updated DESC