SELECT last entry in column as unique from other column

Question

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!

Answer 1

You can do it 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 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.