SELECT last record in a column as unique from another column

Question

Trying to get the latest and unique entry from this table below. It has several doublings, they must 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 

I would like this list to be the last and unique for the "action" for the "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!

Answer 1

You can do this with a subquery. This is the full 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 

Answer 2

You need to use a subquery:

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

A subquery finds the greatest effect for each topic. This data is then used to merge into the same table that you use to get other fields in this "max'd" record.

Answer 3

Sorry, if not correct, read your question. Here's a working request:

 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)