0

I am using MYSQL 5.7. I want to get the recent rows with distinct device_id. I tried these queries:

Query 1

SELECT `table`.`id`, `table`.`device_id` FROM `table` WHERE (id IN (SELECT id FROM (SELECT * FROM table ORDER BY date_modified DESC) AS last_modified GROUP BY device_id) and device_id <> '');

+----+------------------+
| id | device_id        |
+----+------------------+
|  5 | ffcecafe5eed4fba |
|  6 | ffcecafe5eed4fba |
|  8 | 71085f00e527bae0 |
+----+------------------+
3 rows in set (0.00 sec)

But it's not removing the duplicates.

SubQuery 1

SELECT id FROM (SELECT * FROM table ORDER BY date_modified DESC) AS last_modified GROUP BY device_id;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'last_modified.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This gave error. Then I found on MySQL website to use ANY_VALUE() to remove this error.

SubQuery 2

SELECT ANY_VALUE(id) FROM (SELECT * FROM table ORDER BY date_modified DESC) AS last_modified GROUP BY device_id;
+---------------+------------------+
| ANY_VALUE(id) | device_id        |
+---------------+------------------+
|             7 |                  |
|             8 | 71085f00e527bae0 |
|             5 | ffcecafe5eed4fba |
+---------------+------------------+
3 rows in set (0.00 sec)

This is giving distinct ids. But when I am using ANY_VALUE in query 1 above, its giving the same result.

How to query distinct recent rows in MySQL 5.7 ?

Possible Duplicate

MySQL 5.7 return all columns of table based on distinct column

Community
  • 1
  • 1
Ashish Gupta
  • 2,574
  • 2
  • 29
  • 58
  • 1
    See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Nov 25 '16 at 12:02
  • id is neccessory for you? – chirag satapara Nov 25 '16 at 12:03
  • @Strawberry I am not asking for making a query for me, I am getting this error ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'last_modified.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Ashish Gupta Nov 25 '16 at 12:04
  • @chiragpatel Yes. – Ashish Gupta Nov 25 '16 at 12:05
  • with mysql 5.7 is not more allowed the use of group by whitout aggregation function .. .. if you need distinct use a proper way .. a correct related column set – ScaisEdge Nov 25 '16 at 12:15
  • @scaisEdge this is incorrect, only MySQL's **default** behaviour has been changed in this regard, but the behaviour is still configurable. – Shadow Nov 25 '16 at 12:26
  • @shadow correct .. is changed the defaul behaviour and is configurable – ScaisEdge Nov 25 '16 at 12:41

1 Answers1

1

This is the query that worked for me-

SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL;

Thanks @Shadow for the link to the possible duplicate.

Community
  • 1
  • 1
Ashish Gupta
  • 2,574
  • 2
  • 29
  • 58