What is the best way to do the job of Group By in mysql when sql_mode=only_full_group_by

Question

I want to perform a query like to get the last record of any type in DB, at my localhost, I use Maria Db and the query is as follow:

SELECT * 
  FROM table_a
 WHERE column_a=999
    OR column_b=999 
 GROUP 
    BY `group`;

group is a column which I save type in it, for instance: marketing, blog, order, etc

This query works fine on local, but on the server I get the following error:

SQLSTATE[42000]: 
Syntax error or access violation: 
1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
'db_name.table_a.id' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by\n
The SQL being executed was: 
SELECT * FROM `table_a` WHERE (`column_a`=999) OR (`column_b`=999) GROUP BY `group`"

According to MySQL document I can use the following command to make this possible:

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But I don't have the sufficient privilege on Db and get the following error:

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

I asked the hosting to do this for me, they replied that they don't want to do this action

I use the YII2 framework, and now I want a way to add this on the option of database_config.php of the framework or change the query to something else with the same result, and not losing performance

Answer 1

ONLY_FULL_GROUP_BY is a good thing, which enforces basic ANSI SQL rules. Don't change it, fix your code instead.

From there one: you want entire records, so you should not think aggregation, but filtering.

Then: in a database table, records are unordered; for your question to just make sense, you need a column that defines the ordering of rows in each group, so it unambiguous what "last" record mean. Let me assume that you have such column, called id.

Here is a typical approach at this top-1-per-group problem, using a correlated subquery for filtering:

SELECT * 
FROM table_a a
WHERE 
    999 IN (column_a, column_b)
    AND id = (
        SELECT MAX(a1.id) 
        FROM table_a a1 
        WHERE 999 IN (a1.column_a, a1.column_b) AND a1.grp = a.grp
    )

Alternatively, if you are running MySQL 8.0, you can use window functions:

SELECT *
FROM (
    SELECT a.*,
        ROW_NUMBER() OVER(PARTITION BY grp ORDER BY id DESC) rn
    FROM table_a a
    WHERE 999 IN (column_a, column_b)
) a
WHERE rn = 1

Side note: group is a language keyword, hence a poor choice for a column name. I renamed it to grp in the queries.