title: ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause ... this is incompatible with sql_mode=only_full_group_by
body: <p>I am using MYSQL 5.7. I want to get the recent rows with distinct <code>device_id</code>. I tried these queries:</p>
  
  <p><strong>Query 1</strong></p>
  
  <pre><code>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 &lt;&gt; '');
  
  +----+------------------+
  | id | device_id        |
  +----+------------------+
  |  5 | ffcecafe5eed4fba |
  |  6 | ffcecafe5eed4fba |
  |  8 | 71085f00e527bae0 |
  +----+------------------+
  3 rows in set (0.00 sec)
  </code></pre>
  
  <p>But it's not removing the duplicates. </p>
  
  <p><strong>SubQuery 1</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>This gave error. Then I found on MySQL website to use ANY_VALUE() to remove this error.</p>
  
  <p><strong>SubQuery 2</strong></p>
  
  <pre><code>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)
  </code></pre>
  
  <p>This is giving distinct ids. But when I am using ANY_VALUE in query 1 above, its giving the same result.</p>
  
  <p>How to query distinct recent rows in MySQL 5.7 ?</p>
  
  <hr>
  
  <p><strong>Possible Duplicate</strong></p>
  
  <p><a href="https://stackoverflow.com/questions/37293075/mysql-5-7-return-all-columns-of-table-based-on-distinct-column">MySQL 5.7 return all columns of table based on distinct column</a></p>