jitendrapurohit jitendrapurohit - 13 days ago 6
PHP Question

Full Group BY error in order and select clause

I've a temp table which results this -

number contact_id email email_id
8 204 ask@gmail.com 185
8 205 ask@gmail.com 186


I need to fetch the result grouping the query with
email
column.

When I try this -

SELECT number, contact_id, email_id
FROM contact
GROUP BY email
ORDER BY contact_id, email_id


It gives a
FULL_GROUP_BY
error as


SELECT list is not in GROUP BY clause and contains nonaggregated column...


When I modify the select clause to use
ANY_VALUE
keyword as

SELECT ANY_VALUE(number), ANY_VALUE(contact_id), ANY_VALUE(email_id)


It gives an error as -


Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'contact_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by


Is there any way I could group the result by
email
and return only one row ?

Eg either

8 204 ask@gmail.com 185`


OR

8 205 ask@gmail.com 186


Note: I need to do this keeping
only_full_group_by
mode enabled (MySQL 5.7+). Also I can't remove any
order_by
clause.

Answer

I've managed to fix this with below query. If anyone has a better way, please post as an answer.

   SELECT ANY_VALUE(number), ANY_VALUE(contact_id), ANY_VALUE(email_id)
    FROM       contact 
   GROUP BY email
   ORDER BY   MIN(contact_id), MIN(email_id)