选择列中的最后一个条目作为其他列的唯一条目

Trying to get the last and unique record from this table below. There are some doubles in it, those should be filtered out.

试图从下表中获取最后一条独特的记录。有一些双打，那些应该被过滤掉。

id  topic   action  date
1   10127   2   2015-09-24 15:28:30
2   10127   4   2015-09-24 15:29:26
3   10127   2   2015-09-24 15:30:01
4   10127   3   2015-09-24 15:30:55
5   10127   1   2015-09-24 16:07:25
6   10127   5   2015-09-24 16:10:25
7   10127   4   2015-09-24 16:29:26

Using this query (found here) is my best effort but returns only one result.

使用此查询（在此处找到）是我的最大努力，但只返回一个结果。

SELECT MAX(action) as action,topic,date FROM ...... 
GROUP by topic 
ORDER by action DESC

Would like to get this listing as latest entry and unique on 'action' for 'topic':

希望将此列表作为最新条目获取，并在“主题”的“操作”上唯一：

id  topic   action  date
3   10127   1   2015-09-24 15:30:01
4   10127   2   2015-09-24 15:30:55
5   10127   3   2015-09-24 16:07:25
6   10127   4   2015-09-24 16:10:25
7   10127   5   2015-09-24 16:29:26

Hope someone has a solution! Thanks!

希望有人有解决方案！谢谢！

3 个解决方案

#1

You can do it with a subquery. This is the full sqlfiddle: http://sqlfiddle.com/#!9/f7afa/23

您可以使用子查询来完成。这是完整的sqlfiddle：http：//sqlfiddle.com/#！9 / f7afa / 23

Select * FROM (

SELECT
  DISTINCT  `topic`, `action`, `date`
FROM
  ForgeRock      
ORDER by date DESC, action ASC

  ) as X
  GROUP BY action

#2

You need to use a sub-query:

您需要使用子查询：

SELECT *
FROM yourtable
JOIN (
    SELECT topic, MAX(action)
    FROM yourtable
    GROUP BY topic
) AS child ON (yourtable.topic = topic) AND (yourtable.action = child.action)

The subquery finds the largest action for every topic. That data is then used to join against the same table, which you use to fetch the other fields in that "max'd" record.

子查询查找每个主题的最大操作。然后，该数据用于连接同一个表，您可以使用该表来获取“max'd”记录中的其他字段。

#3

Sorry, if have not correct read your question. Here a working query:

对不起，如果没有正确阅读您的问题。这是一个有效的查询：

SELECT id,topic,@action:=@action+1 AS ACTION,DATE
FROM
  ( SELECT t1.id,
           t1.topic,
           t1.date ,
           t2.id AS dup
   FROM tab t1
   LEFT JOIN tab t2 ON t1.action = t2.action
   AND t2.id > t1.id) AS t,

  (SELECT @action:=0) AS tmp
WHERE dup IS NULL;

Result:

结果：

+----+-------+--------+---------------------+
| id | topic | ACTION | date                |
+----+-------+--------+---------------------+
|  3 | 10127 |      1 | 2015-09-24 15:30:01 |
|  4 | 10127 |      2 | 2015-09-24 15:30:55 |
|  5 | 10127 |      3 | 2015-09-24 16:07:25 |
|  6 | 10127 |      4 | 2015-09-24 16:10:25 |
|  7 | 10127 |      5 | 2015-09-24 16:29:26 |
+----+-------+--------+---------------------+
5 rows in set (0.00 sec)

#1