SQL：选择具有相同值的表的一行

Question

我是一位新手，对于SQL还不太熟悉，在我的项目中需要进行一些数据库的排序和过滤：

假设我的数据库如下所示：

==========================================
|  id       |       email        | name
==========================================
|   1       |  [e-mail]      | John
|   2       |  [e-mail]      | Peter
|   3       |  [e-mail]      | Steward
|   4       |  [e-mail]      | Ethan
|   5       |  [e-mail]      | Bob
|   6       |  [e-mail]      | Patrick
==========================================

我该怎么做才能只返回最后一个拥有相同电子邮件的列？

==========================================
|  id       |       email        | name
==========================================
|   3       |  [e-mail]      | Steward
|   5       |  [e-mail]      | Bob
|   6       |  [e-mail]      | Patrick
==========================================

感谢您的提前帮助！

- Z3r0byte

---

正在使用哪个数据库管理系统？ - Vamsi Prabhala

Answer 1

SQL查询：

    SELECT * FROM test.test1  WHERE id IN (
  SELECT MAX(id) FROM test.test1 GROUP BY email
);

希望这可以解决您的问题。谢谢。

- Saibal Roy

Answer 2

这是一个更清晰的方法：

SELECT *
FROM table
ORDER BY email DESC
LIMIT 1;

- I_Shayderov

Answer 3

在SQL中通用的一种做法是使用ANSI标准的row_number()函数：

select t.*
from (select t.*, row_number() over (partition by email order by id desc) as seqnum
      from t
     ) t
where seqnum = 1;

- Gordon Linoff

Answer 4

您可以使用以下查询语句，按email分组获取每个组中id值的最大值：

SELECT email, MAX(id)
FROM mytable
GROUP BY email

使用上述查询作为派生表，您可以获得整个记录：

SELECT t1.*
FROM mytable AS t1
JOIN (
  SELECT email, MAX(id) AS id
  FROM mytable
  GROUP BY email 
) AS t2 ON t1.id = t2.id

- Giorgos Betsos