MySQL - Nonaggregated columns and the ONLY_FULL_GROUP_BY "Strict Mode"

When there is a non-aggregated column in a SELECT clause of a query that uses a GROUP BY clause, what’s the proper way to handle that when the ONLY_FULL_GROUP_BY “Strict Mode” is enabled? Currently I just wrap MAX() around it but I’m sure that’s the incorrect way to handle it.

@r937;

From da manual:

If you know that, for a given data set, each name value in fact uniquely determines the address value, address is effectively functionally dependent on name . To tell MySQL to accept the query, you can use the ANY_VALUE() function:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

Will that also work on other types of database server?

You should probably check their manuals as well :wink:

I mean, the topic does say MySQL :wink:

MAX/MIN is probably the safest database-agnostic aggregation for columns you don’t care about, but at the same time, if you don’t care about them, why select them at all at that point?

1 Like

:trophy: :kiss: :beers: :sunglasses:

sorry, just got up… i’m always late to the party when something is posted on the other side of the world

anyhow, @m_hutley wins this topic today

1 Like

I only ever select columns that I need. The only time that I use SELECT * is just temporary whilst I’m checking that I’ve got the joins for a query correct

this isn’t really about the dreaded, evil “select star”

it’s about this –

the question was, i’ve you don’t care which value of this non-aggregated column you get, why include it?

Here’s an example of where I’m using a non-aggregated column:

				SELECT
					  MAX(t.title) AS thread_title
					, COUNT(p.thread_id) AS post_count
					, MAX(t.id) AS thread_id
					, MAX(lp.thread_id) AS thread_id
					, MAX(lp.last_post_time) AS last_post_time
					, MAX(lp.last_poster_name) AS last_poster_name
					, MAX(lp.last_post_id) AS last_post_id
					, MAX(u.name) AS thread_starter
					, MAX(t.views) AS views
					, MAX(f.id) AS forum_id
				FROM
					ue_forum AS f
				INNER JOIN
					ue_forum_thread AS t
						ON f.id = t.forum_id
				LEFT OUTER JOIN
					ue_forum_post AS p
						ON t.id = p.thread_id
				LEFT OUTER JOIN
					(
						SELECT
							  p.thread_id AS thread_id
							, MAX(p.poster) AS last_poster
							, MAX(u.name) AS last_poster_name
							, MAX(p.posted) AS last_post_time
							, MAX(p.id) AS last_post_id
						FROM
							ue_forum_post AS p
						LEFT OUTER JOIN
							ue_user AS u
								ON u.id = p.poster
						GROUP BY
							thread_id
					) AS lp
						ON lp.thread_id = t.id
				INNER JOIN
					ue_user AS u
						ON t.creator = u.id
				GROUP BY
					  p.thread_id
					, f.id
				HAVING
					forum_id = :forum

those MAXes are not working the way you think they are

for example, in the subquery, MAX(p.poster) AS last_poster is not necessarily the last poster – it’s whatever poster in the entire thread has the highest name

and in the outer query, you’re grouping by forum and thread, and yet the SELECT clause doesn’t show these!! you’ll get one row per forum/thread and you won’t know which is which

i think your entire approach here needs to be redesigned

1 Like