MySQL : How TO Use ANY_VALUE with all the column of DB table

Question

Facing the issue of

incompatible with sql_mode=only_full_group_by;

to resolve the issue, now use ANY_VALUE(<column_name>), It resolves the issue.

But, in some mysql query, We have to use all the columns of db table; where I have used * like :

SELECT * FROM db_table GROUP BY Col_1;

Now How can I apply ANY_VALUE, to the above query.

Please guide.

SELECT ANY_VALUE(*) FROM db_table GROUP BY Col_1;

Throws error.

`any_value()` for all columns ``, like `any_value(column1), any_value(column2), ..., any_value(columnn)`? — sticky bit, Jul 22 '19 at 10:32
`SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));` more details can be found [here](https://stackoverflow.com/questions/23921117/disable-only-full-group-by) — danish-khan-I, Jul 22 '19 at 10:35
@danish-khan-I I did the same; but after server restart, ONLY_FULL_GROUP_BY is enabled again. — Sachin, Jul 22 '19 at 12:35

score 4 · Answer 1 · answered Jul 22 '19 at 10:49

4

If you just a need value you must apply the any_value() function to each column you want select eg:

    SELECT Col_1, any_value(col_2), any_value(col3) 
    FROM db_table 
    GROUP BY Col_1;

answered Jul 22 '19 at 10:49

ScaisEdge

Is there any method where one by one column is not mentioned? Something like SELECT ANY_VALUE(*). Please confirm. – Sachin Jul 22 '19 at 12:42
I confirm .. you can't use any_value(*) .. you must explicitally use each column name you want select .. – ScaisEdge Jul 22 '19 at 12:44

score 0 · Answer 2 · edited Jul 23 '19 at 05:41

0

Try using any_value:

 SELECT any_value(t.col) 
    FROM table as t
    GROUP BY t.Col_1;

edited Jul 23 '19 at 05:41

iamdanchiv

answered Jul 22 '19 at 12:14

Crysis

2 Answers2