| 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`
10