'Show only rows sorted by Field 1 where Field 2 includes the first distinct value

I want to create a statement which results in a table sorted by Field 1, showing only the rows where a distinct value of Field 2 appears for the first time.

Since my case is a bit hard to describe, here a visualization of what I am looking for.

My table:

ID Field 1 Field 2
1 AA ttt
2 AA ttt
3 AA uuu
4 AA vvv
5 AA vvv
6 BB xxx
7 BB xxx

Desired output:

ID Field 1 Field 2
1 AA ttt
3 AA uuu
4 AA vvv
6 BB xxx
mysqlsql


Solution 1:[1]

If your MySQL version support ROW_NUMBER window function you can try this.

SELECT id,Field1,Field2
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY Field1,Field2 ORDER BY ID) rn
    FROM T
) t1
WHERE rn = 1

or try to use self-join with subquery

SELECT t2.*
FROM (
    SELECT MIN(ID) Id
    FROM T
    GROUP BY Field1,Field2
) t1 INNER JOIN t2
ON t1.Id = t2.Id

Solution 2:[2]

One simple way would be to filter based in the aggregate of Field2

sorting just by field1 however is ambugious, for your desired results you need to sort by both columns, or the id column.

select * 
from t
where t.id in (select Min(id) from t group by field2)
order by field1, field2;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 D-Shih
Solution 2 Stu

Related Questions