Full Group BY error in order and select clause

Question

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.

Use `SELECT number, contact_id, email_id, email FROM contact GROUP BY email ORDER BY contact_id, email_id` — RJParikh, Nov 23 '16 at 06:36
Thanks, but that will error in select list not in group by as contact_id is not in group by and if we do that - it will result both the rows, I want any one row to be returned — jitendrapurohit, Nov 23 '16 at 06:37
Use : `SELECT min(number), email FROM contact GROUP BY email ORDER BY contact_id, email_id` — RJParikh, Nov 23 '16 at 06:41
Tried that too - that results in `ORDER BY clause is not in GROUP BY clause` — jitendrapurohit, Nov 23 '16 at 06:42
Can you please edit your question and add what exactly you expect the result look like?! How exactly do you want to combine two rows in 1 row?! because it seems to be a very simple query! — EhsanT, Nov 25 '16 at 12:07

jitendrapurohit · Accepted Answer · 2016-11-25T13:06:28.030

0

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)

edited Nov 25 '16 at 13:06

answered Nov 23 '16 at 07:09

jitendrapurohit

9,435
2
28
39

Full Group BY error in order and select clause

1 Answers1

Linked