jitendrapurohit jitendrapurohit - 10 months ago 52
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

When I try this -

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

It gives a
error as

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

When I modify the select clause to use
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
and return only one row ?

Eg either

8 204 ask@gmail.com 185`


8 205 ask@gmail.com 186

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

Answer Source

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)