MySQL - Select by latest date using unique combination of two columns

[Wayder]

Table: t_cost
+------------+-----+-----+------+
| DateActive | TID | WID | Cost |
+------------+-----+-----+------+
| 2021-05-01 | 1 | 1 | 5.05 | --->
| 2021-05-01 | 1 | 2 | 5.08 | |
| 2021-05-01 | 2 | 1 | 5.10 | |
| 2021-05-01 | 2 | 2 | 6.00 | |
| 2021-05-08 | 1 | 1 | 5.10 | <-- New Cost
+------------+-----+-----+------+

TID and WID are a unique combination but I can't make the table UNIQUE as I need to hold historical costs.

I have tried many a combination including sub queries but this is where I am at.
SELECT MAX(DateActive) AS DateActive,Cost,TID,WID FROM t_cost GROUP BY TID,WID

The result from this is:
+------------+-----+-----+------+
| DateActive | TID | WID | Cost |
+------------+-----+-----+------+
| 2021-05-08 | 1 | 1 | 5.05 | <-- should be 5.10
| 2021-05-01 | 1 | 2 | 5.08 |
| 2021-05-01 | 2 | 1 | 5.10 |
| 2021-05-01 | 2 | 2 | 6.00 |
+------------+-----+-----+------+

Do you know how to select the latest date using the unique combination of TID and WID?

Result required:
+------------+-----+-----+------+
| DateActive | TID | WID | Cost |
+------------+-----+-----+------+
| 2021-05-08 | 1 | 1 | 5.10 | <--
| 2021-05-01 | 1 | 2 | 5.08 |
| 2021-05-01 | 2 | 1 | 5.10 |
| 2021-05-01 | 2 | 2 | 6.00 |
+------------+-----+-----+------+

Thank you

[robzilla]

MAX() only affects the data in that particular column, not the row selection. I think you'll need to join with a subquery.

I put this together quickly, not sure if it's optimal, but this should get you the result required:

SELECT t_cost.DateActive, t_cost.TID, t_cost.WID, t_cost.Cost FROM t_cost
INNER JOIN (SELECT TID, WID, MAX(DateActive) as DateActive
FROM t_cost
GROUP BY TID, WID) AS MaxDates
ON MaxDates.TID = t_cost.TID
AND MaxDates.WID = t_cost.WID
AND MaxDates.DateActive = t_cost.DateActive
GROUP BY TID, WID

[Wayder]

This is what I eventually ended up with.

SELECT DateActive,TID,WID,Cost
FROM t_cost
WHERE (DateActive,TID,WID) IN
(
SELECT MAX(DateActive),TID,WID
FROM t_cost
GROUP BY TID,WID
)
ORDER BY TID,WID

Thank you for your help.