GROUP BY is incompatible with sql_mode=only_full_group_by'

Question

I have a table called monitor_links with the following columns:

This table is used to store links from others websites. I'm getting those links using this query:

SELECT title, url
FROM monitor_links
WHERE id_domain = 5
GROUP BY url 
ORDER BY date_created asc

With that query I return the title and url from the storage link (where id_domain is equal to 5). But in my case, can exists rows with same url, and to not return same repeated url I'm using GROUP BY url. (If exists more than one row with same URL and id_domain = 5, my code returns only one of this rows).

The problem is that I'm getting the following error:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shuri.monitor_links.title' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by'.

Some people said that I can try to disable the only_full_group_by, but I want to fix my query rather than silencing the warning.

What happens if there are 2 entries with the same `url`, but different `title` values? — Alex McMillan, Oct 05 '17 at 02:18
Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — Robert Moskal, Oct 05 '17 at 02:20
What is the point of the GROUP BY here? You're not grouping anything - there's no COUNT, no SUM, nothing which requires a GROUP BY. What are you attempting accomplish with this query? — Bob Jarvis - Слава Україні, Oct 05 '17 at 02:22
@BobJarvis He's just trying to suppress multiple rows for the same URL. — Barmar, Oct 05 '17 at 02:23
@BobJarvis It's in the question: **to not return same repeated url I'm using GROUP BY url** — Barmar, Oct 05 '17 at 02:24

score 4 · Answer 1 · answered Oct 05 '17 at 02:22

You can use the ANY_VALUE function to tell MySQL that you want to return any value within the group.

SELECT ANY_VALUE(title) AS title, url
FROM monitor_links
WHERE id_domain = 5
GROUP BY url

This is essentially equivalent to turning off ONLY_FULL_GROUP_BY temporarily for this query.

score 1 · Answer 2 · answered Oct 05 '17 at 02:19

If you want one title for each url, then:

SELECT MAX(title) as title, url
FROM monitor_links
WHERE id_domain = 5
GROUP BY url ;

If you want the most recent version:

SELECT ml.*
FROM monitor_links ml
WHERE ml.created_date = (SELECT MAX(ml2.created_date)
                         FROM monitor_links ml2
                         WHERE ml2.url = ml.url AND ml2.id_domain = ml.id_domain
                        );

GROUP BY is incompatible with sql_mode=only_full_group_by'

2 Answers2