MySQL 按降序排列

Question

我有一个不包含唯一列的表

id | fid | ver | dir

1 || 1 || 1 || 3

2 || 2 || 1 || 2

3 || 3 || 1 || 3

4 || 3 || 2 || 3

5 || 4 || 1 || 4

6 || 5 || 1 || 5

我的问题是如何在 dir = 3 中选择最新 fid(基于 ver)的 ID

我尝试了(SELECTidFROMtableWHEREdir='3' GROUP BYfid ORDER BYversion) 但它给了我

id

1

3

这不是我想要得到的结果。这是我期望的结果:

id

1

4

Answer 1

select id
from myTable t1
where dir = 3
and not exists (
    select 1
    from myTable t2
    where t1.version < t2.version
    and t1.dir = t2.dir
    and t1.fid = t2.fid
)
order by version