How do I select records with max from id column if two of three other fields are identical

Question

I have a table that stores costs for consumables.

consumable_cost_id  consumable_type_id  from_date   cost
1                   1                   01/01/2000  £10.95
2                   2                   01/01/2000  £5.95
3                   3                   01/01/2000  £1.98
24                  3                   01/11/2013  £2.98
27                  3                   22/11/2013  £3.98
33                  3                   22/11/2013  £4.98
34                  3                   22/11/2013  £5.98
35                  3                   22/11/2013  £6.98

If the same consumable is updated more than once on the same day I would like to select only the row where the consumable_cost_id is biggest on that day. Desired output would be:

consumable_cost_id  consumable_type_id  from_date   cost
1                   1                   01/01/2000  £10.95
2                   2                   01/01/2000  £5.95
3                   3                   01/01/2000  £1.98
24                  3                   01/11/2013  £2.98
35                  3                   22/11/2013  £6.98

Edit:

Here is my attempt (adapted from another post I found on here):

SELECT cc.* 
FROM 
    consumable_costs cc 
    INNER JOIN 
    ( 
        SELECT 
            from_date, 
            MAX(consumable_cost_id) AS MaxCcId 
        FROM consumable_costs 
        GROUP BY from_date 
    ) groupedcc 
        ON cc.from_date = groupedcc.from_date 
            AND cc.consumable_cost_id = groupedcc.MaxCcId

Answer 1

You were very close. This seems to work for me:

SELECT cc.*
FROM 
    consumable_cost AS cc
    INNER JOIN
    (
        SELECT 
            Max(consumable_cost_id) AS max_id, 
            consumable_type_id,
            from_date
        FROM consumable_cost
        GROUP BY consumable_type_id, from_date
    ) AS m
        ON cc.consumable_cost_id = m.max_id

Answer 2

SELECT * FROM consumable_cost 
GROUP by consumable_type_id, from_date
ORDER BY cost DESC;

Answer 3

Assuming consumable_cost_id is unique.

SELECT * FROM T t1
WHERE EXISTS(
    SELECT t2.consumable_type_id, t2.from_date FROM T t2
    GROUP by t2.consumable_type_id, t2.from_date
    HAVING MAX(t2.consumable_cost_id) = t1.consumable_cost_id);

Because of comment that this was returning an incorrect result, I created a test-query for Oracle that proves that this query works. As I said, it's for Oracle, but there is really no reason why this should not work in MS Access. The only Oracle specific I used here is the FROM DUAL to generate the virtual data.

WITH T AS
(
SELECT 1 AS consumable_cost_id,1 AS consumable_type_id, TO_DATE('01/01/2000','DD/MM/YYYY') AS FROM_DATE, '£10.95' AS COST FROM DUAL
UNION ALL
SELECT 2,2,TO_DATE('01/01/2000','DD/MM/YYYY'),'£5.95' FROM DUAL
UNION ALL
SELECT 3,3,TO_DATE('01/01/2000','DD/MM/YYYY'),'£1.98' FROM DUAL
UNION ALL
SELECT 24,3,TO_DATE('01/11/2013','DD/MM/YYYY'),'£1.98' FROM DUAL
UNION ALL
SELECT 27,3,TO_DATE('22/11/2013','DD/MM/YYYY'),'£1.98' FROM DUAL
UNION ALL
SELECT 33,3,TO_DATE('22/11/2013','DD/MM/YYYY'),'£1.98' FROM DUAL
UNION ALL
SELECT 34,3,TO_DATE('22/11/2013','DD/MM/YYYY'),'£1.98' FROM DUAL
UNION ALL
SELECT 35,3,TO_DATE('22/11/2013','DD/MM/YYYY'),'£1.98' FROM DUAL
)
SELECT * FROM T t1
WHERE EXISTS(
    SELECT t2.consumable_type_id, t2.from_date FROM T t2
    GROUP by t2.consumable_type_id, t2.from_date
    HAVING MAX(t2.consumable_cost_id) = t1.consumable_cost_id);

Result:

1    1   01-JAN-00   £10.95
2    2   01-JAN-00   £5.95
3    3   01-JAN-00   £1.98
24   3   01-NOV-13   £1.98
35   3   22-NOV-13   £1.98