+--------+-------+-----+--------+
| Product| Lot # | Date    |Cost|  
+--------+-------+-----+--------+
| Apples | 1     | 05/24/24| 30 |
| Apples | 2     | 05/23/24| 29 |
| Pears  | 3     | 05/22/24| 28 |
| Pears  | 4     | 05/21/24| 27 |
| Berries| 5     | 05/20/24| 26 |
| Berries| 6     | 05/19/24| 25 |
| Apples | 7     | 05/18/24| 24 |
| Apples | 8     | 05/17/24| 23 |
| Pears  | 9     | 05/16/24| 22 |
| Pears  | 10    | 05/15/24| 21 |
| Berries| 11    | 05/14/24| 20 |
| Berries| 12    | 05/13/24| 19 |
+--------+-------+-----+--------+

Results I would like to have

+--------+-------+-----+--------+-----------+-----------+----------+
| Product| Lot # | Date    |Cost| Last Lot #| Last Date | Last Cost| 
+--------+-------+-----+--------+-----------+-----------+----------+
| Apples | 1     | 05/24/24| 30 | 2         | 05/23/24  | 29       |
| Pears  | 3     | 05/22/24| 30 | 4         | 05/21/24  | 29       |
| Berries| 5     | 05/20/24| 30 | 6         | 05/19/24  | 29       |
+--------+-------+-----+--------+-----------+-----------+----------+

My goal is to get the latest 2 lots of any product based on the dates
Would also be nice if I could do some comparison math in a separate column but I would be happy with any help….

This is what I got so far but not sure how to account for the dates or even if this is in the right direction.

SELECT
    `productNumber`,
    `unitCost`,
    `purchaseOrder`,
    `productDesc`
FROM
    (
    SELECT
        `productNumber`,
        `unitCost`,
        `purchaseOrder`,
        `productDesc`,
        (
            @rn := IF(@prev = `productNumber`, @rn +1, 1)
        ) AS rownumb,
        @prev := `productNumber`
    FROM
        (
        SELECT
            `productNumber`,
            `unitCost`,
            `purchaseOrder`,
            `productDesc`
        FROM
            `inboundDetails`
        ORDER BY
            `productNumber`
        DESC
            ,
            `unitCost`
    ) AS sortedlist
JOIN(
SELECT
    @prev := NULL,
    @rn := 0
) AS vars
) AS groupedlist
WHERE
    rownumb <= 2
ORDER BY
    `productNumber`
DESC
    ,
    `unitCost`