How can I query my table to group it by 2 fields in mySQL?

I’m stuck.

I’m trying to query one of my tables to obtain the maximum ‘canister_change_date’ with grouped pairs ‘canister_type’ and ‘test_cell’.

I’ve put together a table with some dummy data (below) If you want the create table schema, let me know and I’ll put it in the comments.

The final result would either need to have the id’s or the whole row with id.

expected result (below) would have id’s – 1, 2, 3, 5, 7, 8

6 should be removed as matching pair (test_cell =4, canister_type=Carbon Monoxide) and 7 to be taken as it has the later ‘canister_change_date’ date.

The expect result would either need to have the id’s or id’s and rest of fields.

Thanks!

Answer

With NOT EXISTS:

select t.* from tablename
where not exists (
  select 1 from tablename
  where test_cell = t.test_cell and canister_type = canister_type
    and canister_change_date > t.canister_change_date
)

or if your version of MySql is 8.0+ and supports window functions:

select t.* from (
  select *, 
    row_number() over (partition by test_cell, canister_type order by canister_change_date desc) rn
  from tablename
) t
where t.rn = 1