Selecting distinct 5 columns combination in mysql

Question

I have a mysql table that looks like this: Col 1 is UNIQUE.

1   value1    value2    0    2
2   value1    value2    1    3
3   value3    value4    3    2
4   value4    value5    4    1
5   value3    value4    3    1

I need a query to select all the rows with distinct column 1 and 2, for example the output I want for this example will look like this:

1   value1    value2    0    2
3   value3    value4    3    2
4   value4    value5    4    1

I need distinct col 1 and 2 but altogether all columns combination will be distinct always. I want to display distinct col 1,2 and 3 without col 2,3 repeating.

I've found a few samples on how to do it but they all select distinct on each column individually. I tried many stackoverflow answers too. But my question is different.

Answer 1

One method that performs well is a correlate subquery:

select t.*
from t
where t.col1 = (select min(t2.col1)
                from t t2
                where t2.col2 = t.col2 and t2.col3 = t.col3
               );

For best performance, you want an index on (col2, col3, col1).

I strongly advise having a primary key on all tables, but if you did not have one, then row_number() would be the way to go:

select t.*
from (select t.*,
             row_number() over (partition by col2, col3 order by col2) as seqnum
      from t
     ) t
where seqnum = 1;

This incurs a tad more overhead because row numbers need to be assigned to all rows before they are filtered for only first one.

Answer 2

It could be achieved by using ROW_NUMBER:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY col2, col3 ORDER BY col1) AS rn
      FROM tab) sub
WHERE rn=1