Selecting each first unique tuple of columns from a MySQL table

I have a log table in MySQL (5.7.14) with the following schema:

CREATE TABLE logs
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  entry_date DATE NOT NULL,
  original_date DATE NOT NULL,
  ref_no VARCHAR(30) NOT NULL
) Engine=InnoDB;

INSERT INTO logs VALUES
(1,'2020-01-01','2020-01-01','XYZ'),
(2,'2020-01-01','2020-01-01','ABC'),
(3,'2020-01-02','2020-01-01','XYZ'),
(4,'2020-01-02','2020-01-01','ABC'),
(5,'2020-01-03','2020-01-02','XYZ'),
(6,'2020-01-03','2020-01-01','ABC');

I want to return the first row for each unique (original_date, ref_no) pairing, where 'first' is defined as 'lowest id'.

For example, if I had the following data:

id|entry_date|original_date|ref_no
--+----------+-------------+------
1 |2020-01-01|2020-01-01   |XYZ
2 |2020-01-01|2020-01-01   |ABC
3 |2020-01-02|2020-01-01   |XYZ
4 |2020-01-02|2020-01-01   |ABC
5 |2020-01-03|2020-01-02   |XYZ
6 |2020-01-03|2020-01-01   |ABC

I would want the query to return:

id|entry_date|original_date|ref_no
--+----------+-------------+------
1 |2020-01-01|2020-01-01   |XYZ
2 |2020-01-01|2020-01-01   |ABC
5 |2020-01-03|2020-01-02   |XYZ

In other words:

• Row 1 is returned because we haven't seen 2020-01-01,XYZ before.
• Row 2 is returned because we haven't seen 2020-01-01,ABC before.
• Row 3 is not returned because we have seen 2020-01-01,XYZ before (row 1).
• Row 4 is not returned because we have seen 2020-01-01,ABC before (row 2).
• Row 5 is returned because we haven't seen 2020-01-02,XYZ before.
• Row 6 is not returned because we have seen 2020-01-01,ABC before (row 2).

Is there a way to do this directly in SQL? I've considered DISTINCT but I think that only returns the distinct columns, whereas I want the full row.

You can use a correlated subquery:

select l.*
from logs l
where l.id = (select min(l2.id)
              from logs l2
              where l2.original_date = l.original_date and
                    l2.ref_no = l.ref_no
             );

For performance, you want an index on logs(original_date, ref_no, id).

To avoid a correlated subquery you can do:

select l.*
from logs l
join (
  select original_date, ref_no, min(id) as min_id
  from logs
  group by original_date, ref_no
) x on l.id = x.min_id

Try this:

select t1.*
from logs AS t1
left join logs AS t2 on 
(
  t2.original_date = t1.original_date and
  t2.ref_no = t1.ref_no and
  t2.id < t1.id
)
where
    t2.original_date is null and
    t2.ref_no is null