2

I need to execute the below Query but not able to do it.

SELECT * 
FROM (SELECT * 
      FROM rider_status 
      order by created_date DESC) as riderActice 
INNER JOIN rider_user ON rider_userId = rider_user.id 
Where rider_user.isActive = 1 
AND online_status = 1 
GROUP by rider_userId

Error: "#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'riderActice.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

I have read some blogs and found the below solution.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But Yet executing above solution I am getting another issue which is 

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Please let me know if I am missing something or doing it wrong

sofquestion 9
  • 91
  • 1
  • 8
  • show us the error message – Abdulla Nilam Feb 24 '20 at 07:43
  • The errors messages which you show us means that the queries even were not sent to MySQL - that is NOT MySQL error messages. So do not try to alter something on MySQL side - this makes no sense. Or maybe you use some errorneous method for to obtain the error message - fix it. – Akina Feb 24 '20 at 07:47
  • Why do you need a `GROUP BY`? you are not using any aggregation functions. – Nick Feb 24 '20 at 07:47
  • @Akina Then what to do to use group by in MySQL – sofquestion 9 Feb 24 '20 at 08:25
  • @sofquestion9 I don't see any reason to worry about any problem with GROUP BY until the problem of sending the query to the MySQL server is resolved. – Akina Feb 24 '20 at 08:29
  • @Akina When I run a simple query (where I am using a single table) with the group by its work. When I am using it with left join (with multiple table) this error is shown why? error "this is incompatible with sql_mode=only_full_group_by" – sofquestion 9 Feb 24 '20 at 08:35
  • @sofquestion9 Now compare your last message and error messages posted in a question... then edit the question and post error messages strictly, without any edition. – Akina Feb 24 '20 at 08:38
  • @Akina Done. Can you help me to solve it ? – sofquestion 9 Feb 24 '20 at 08:41
  • @sofquestion9 I do NOT see the words "this is incompatible with sql_mode=only_full_group_by" in the error messages in your question, I see only queries texts copies... – Akina Feb 24 '20 at 08:44
  • Anycase as a palliative solution you may execute `SET @@sql_mode := REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','');` immediately before the query. But I'd recommend to pay attention to [@VBoka](https://stackoverflow.com/users/6565038/vboka)'s answer. – Akina Feb 24 '20 at 08:47
  • @Akina I had edit it now Please have a look. – sofquestion 9 Feb 24 '20 at 08:50
  • Well. Now think. You group by `rider_userId`. There exists some records with the same `rider_userId`. You'll obtain the same `rider_userId` value from any record in a group, but the values in another fields differs in this group. Server will return a random value for each field from all possible values in a group. Do you really need in random values? – Akina Feb 24 '20 at 08:52
  • @Akina No, I don't want random values – sofquestion 9 Feb 24 '20 at 08:55
  • If so you must define what value from all possible values yu'd like to get, and use proper expression for each separate returned field. Anycase the asterisk in output will be replaced with a lot of separate fields expressions. – Akina Feb 24 '20 at 08:58
  • PS. ORDER BY in the subquery makes no sense - it will be ignored by the server because no order-dependent construction (LIMIT, for example) in this subquery. It may be removed freely without any consequence. – Akina Feb 24 '20 at 09:13

1 Answers1

1

You can not select all(* means all) columns from a table and do a group by one column. That is why you are getting an error. If the column is in the select clause and it is not a part of aggregate function then it has to be in a group by clause.

Also, use the aliases you have created riderActice when joining two tables(subquery and a table).

Here is a small demo demonstrating first part of my answer. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b10244e667e59bffb146170014dc69ba

If you want to select all columns then do it like this:

SELECT riderActice.rider_userId
         , riderActice.created_date
         , rider_user.id
         , rider_user.isActive
         , rider_user.online_status 
FROM rider_status as riderActice 
INNER JOIN rider_user ON riderActice.rider_userId = rider_user.id 
WHERE rider_user.isActive = 1 
AND rider_user.online_status = 1 
GROUP BY riderActice.rider_userId
         , riderActice.created_date
         , rider_user.id
         , rider_user.isActive
         , rider_user.online_status

If you do not want to group by every column then explain to us what is it that you need, but this is the answer to your question.

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • Why I can not? This is working perfectly in my one hosting. I have moved my database to other after that this starting showing this issue – sofquestion 9 Feb 24 '20 at 08:24
  • I am not able to use a simple group by in my MySQL – sofquestion 9 Feb 24 '20 at 08:28
  • I do not know what have you used before and how but this is the fact. I will add a small demo so you can see yourself... Also, please update your error from your question. The main part is missing. – VBoka Feb 24 '20 at 08:59
  • is there any other way because I can't change so many queries as I am migrating my server. – sofquestion 9 Feb 24 '20 at 11:50
  • Hi @sofquestion9 is there any other way to do what ? We do not know what is it you atr trying to do ? As others already commented you do not need group by because you are not using aggregation functions (sum(), max() and so on...). – VBoka Feb 24 '20 at 12:11