mysql - 如何为 ```GROUP BY``` 列返回最高 ID NON NULL 字段？

Question

SELECT email, first_name, last_name from person group_by email order by id desc

如果 first_name 混合了值和空值，我将如何获得该列的最新 NOT NULL 值？

id | email   | first_name   | last_name
1  | [e-mail] |  ted         | smith
2  | [e-mail] |  zed         | smith
3  | [e-mail] |  NULL        | johnson

我基本上想获得 first_name 的值 zed。

标签： mysqlsql

Answer 1

假设您有一个person_id具有排序的列，您可以简单地执行以下操作：

select p.*
from person p
where (email, person_id) in (select p2.email, max(p2.person_id)
                             from person p2
                             where p2.first_name is not null
                             group by p2.email
                            );