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:

Desired output:

CodePudding user response：

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

CodePudding user response：

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;

CodePudding user response：

One way of doing that efficiently is by using the ROW_NUMBER window function, that allows you to define a unique numeric value in each partition you split the values in. If you order by ID and partition by Field 1 and Field 2, and filter by the rank=1 values, you should gain the rows you want.

You'll find syntax on this window function here: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number. I'd highly recommend to look also to the analogous functions RANK and DENSE_RANK at the same link.