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.
From da manual:
If you know that, for a given data set, each
namevalue in fact uniquely determines the
addressis effectively functionally dependent on
name. To tell MySQL to accept the query, you can use the
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
I mean, the topic does say MySQL
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?
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
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