Get distinct data based on min time MYSQL

Question

I have a sample data here:

And here is my desired output:

How can i get the distinct value based on min time?

here is the update i tried

Answer 1

A correlated subquery for filtering is probably the easiest solution:

select t.*
from t
where t.timestamp = (select min(t2.timestamp)
                     from t t2
                     where t2.id = t.id
                    );

You may need to take the date into account if you want the earliest record overall:

select t.*
from t
where (t.date, t.time) in (select t2.date, t2.time
                           from t t2
                           where t2.id = t.id
                           order by t2.date desc, t2.time desc
                          );

or if you want the earliest record on each date:

select t.*
from t
where t.timestamp = (select min(t2.timestamp)
                     from t t2
                     where t2.id = t.id and
                           t2.date = t.date
                    );

Answer 2

With MySQL 8.0, window functions are usually the most efficient way to proceed:

SELECT col1, col2, col3, col4, col5
FROM (
    SELECT t.*, ROW_NUMBER() OVER(PARTITION BY col2 ORDER BY col4) rn
    FROM mytable t
) x WHERE rn = 1

With earlier versions, I would use a NOT EXISTS condition with a correlated subquery:

SELECT *
FROM mytable t
WHERE NOT EXISTS (
    SELECT 1 FROM mytable t1 WHERE t1.col2 = t.col2 AND t1.col4 < t.col4 
)

Demo on DB Fiddle:

| col1 | col2 | col3       | col4     | col5       |
| ---- | ---- | ---------- | -------- | ---------- |
| 2    | AAA  | Customer 1 | 07:00:00 | 2019-03-04 |
| 3    | BBB  | Customer 2 | 15:00:00 | 2019-03-04 |

For this to perform efficiently, you would need an index on mytable(col2, col4):

CREATE INDEX mytable_idx ON mytable(col2, col4);

---

If you have more than one record with the same col1 and col2, you can add an additional criteria to avoid duplicates in the resultset, using column c1, which I understand is the primary key of the table:

SELECT *
FROM mytable t
WHERE NOT EXISTS (
    SELECT 1 
    FROM mytable t1 
    WHERE 
        t1.col2 = t.col2 
       AND (
           t1.col4 < t.col4 
           OR (t1.col4 = t.col4 AND t1.col1 < t.col1)
       )
)

Updated DB Fiddle.