A
A
Alexey Ivanov2017-11-28 16:03:35
MySQL
Alexey Ivanov, 2017-11-28 16:03:35

I don't understand how to rewrite a complex sql query after moving the site to another server?

Hello.
There was a problem. In the site admin panel, using a sql query, a list of user dialogs is displayed, and the selection is aimed at displaying unique dialogs, that is, for example, user1 and user2 (all their joint messages) are combined into one line, which contains their names, id and the time of the last general message. Here is the request code:

$query1 = "SELECT DISTINCT IF(fromid < toid, fromid, toid) AS user1,IF(fromid >= toid, fromid, toid) AS user2, data FROM cmsa22_b_chat AS ccc ORDER BY data DESC";
    $rst=mysqli_query($db,"SELECT user1,user2 FROM ($query1) AS rows GROUP BY user1,user2 ORDER BY data DESC LIMIT 100");

By the way, this code was compiled thanks to more experienced colleagues from toster.ru, for which we bow to them once again! (sorry for offtopic)
The site was recently moved to a more modern server. After the migration, MYSQL began to give the following error:
Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'rows.data' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=
only_full_group_by I will be grateful for advice.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
B
Boris Korobkov, 2017-11-28
@mg15

In addition to vism
's answer Option 3: choose an ID (which is in group by), and only then use a separate request for the rest of the information.
Option 4: For other fields (which are not in group by) use aggregate functions.

V
vism, 2017-11-28
@vism

The ORDER BY clause expression #1 is not in the GROUP BY clause and contains a non-aggregated column "rows.data" that is functionally independent of the columns in the GROUP BY clause; this is incompatible with sql_mode = only_full_group_by
sql_mode = only_full_group_by chop off
or
add rows.data to the group by
You would really at least google it, it's faster than writing a question here, after all.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question