MySQL - Run distinct command on certain columns

Question

I'm trying to run a distinct on four columns in the query below:

select
  full_records.id,
  full_records.domain_id,
  subdomains.name as subdomain_name,
  types.name as type_name,
  changelog.content as content,
  changelog.changed_on
from full_records
  inner join subdomains on full_records.subdomain_id = subdomains.id
  inner join types on full_records.type_id = types.id
  inner join changelog on full_records.id = changelog.full_record_id
where
  full_records.domain_id = 2
order by changelog.changed_on desc

and this returns the following:

I'm not sure how to go about altering the query so that it only returns the records that are unique across these four fields.

full_records.domain_id,
subdomains.name as subdomain_name,
types.name as type_name,
changelog.content as content

So if they were unique across those four fields, the rows 2, 3, 4 and 7 would not be in the results. It's basically to identify the latest change for a domain record. Any help would be really appreciated. Thanks.

Answer 1

One pretty simple method is row_number():

with cte as (
      select fr.id, fr.domain_id, sd.name as subdomain_name,
             t.name as type_name, cl.content, cl.changed_on
      from full_records fr join
           subdomains sd
           on fr.subdomain_id = sd.id join
           types t
           on fr.type_id = t.id join
           changelog cl
           on fr.id = cl.full_record_id
      where fr.domain_id = 2
     )
select cte.*
from (select cte.*, 
             row_number() over (partition by domain_id, subdomain_name, type_name, content
                                order by changed_on desc
                               ) as seqnum
      from cte
     ) cte
where seqnum = 1;

Note that I added table aliases so the query is easier to write and to read.