MySQL 获取特定用户的最低 ID - 或 - 每行的紧急程度最高的条目的 ID

Question

我有以下数据库

id | user | urgency | problem          | solved

里面的信息有不同的用户，但是这些用户都有多个条目

1  | marco | 0      | MySQL problem    | n
2  | marco | 0      | Email problem    | n
3  | eddy  | 0      | Email problem    | n 
4  | eddy  | 1      | MTV doesn't work | n
5  | frank | 0      | out of coffee    | y

我想做的是:通常我会先检查每个人最老的问题。我使用此查询来获取最早问题的 ID。

select min(id) from db group by user

这为我提供了最旧问题 ID 的列表。但我希望人们能够让某个问题变得更加紧迫。我想要每个用户最紧急的ID，或者最紧急的问题的ID

获取 max(urgency) 不会给出问题的 ID，它会给我 max urgency。

明确一点:我想得到这个结果

row  |   id
0    |   1
1    |   4

最后一个条目应该在结果中，因为它已经解决了

Answer 1

Select ...
From SomeTable As T
    Join    (
            Select T1.User, Min( T1.Id ) As Id
            From SomeTable As T1
                Join    (
                        Select T2.User, Max( T2.Urgency ) As Urgency
                        From SomeTable As T2
                        Where T2.Solved = 'n'
                        Group By T2.User
                        ) As MaxUrgency
                    On MaxUrgency.User = T1.User
                        And MaxUrgency.Urgency = T1.Urgency
            Where T1.Solved = 'n'
            Group By T1.User
            ) As Z
        On Z.User = T.User
                And Z.Id = T.Id