CODEWITHSUNDEEP
phpmysql
KJS
KJS

Reputation: 1214

SELECT last entry in column as unique from other column

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!

Upvotes: 5

Views: 93

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

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)

Upvotes: 1

Marc B
Marc B

Reputation: 360672

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.

Upvotes: 1

Gabe
Gabe

Reputation: 1183

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

Upvotes: 4

Related Questions