2

I am trying to run below query on my sql:

SELECT `post_id` , `userto`, `userfrom` , noti_type, id FROM tbl_notification WHERE userto = '2' GROUP BY post_id,noti_type

But it is giving me this error:

Error Code: 1055. Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'vinpedb.tbl_notification.userfrom' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Now the problem is I am not able to remove "only_full_group_by" from sql_mode, so that is not an option as i dont have the rights to do so.

Is there anything I can do to modify the query to fit the new rules and support only_full_group_by too?

I am trying to group below content:

id  userto  userfrom  post_id  noti_type

1     2        4        4       post_like
2     2        1        3       post_like
3     2        4        3       post_like
4     2        8        3       post_like
14    2        7        3       post_comm
15    2        12       3       post_comm
19    2        4        5       post_like

results should be

id  userto  userfrom  post_id  noti_type

1     2        4        4       post_like
4     2        8        3       post_like
15    2        12       3       post_comm
19    2        4        5       post_like

(Please dopnt mark this as duplicate if you cant answer the question, as this is not, in all other posts people have only 1 solution to change sql mode which I am not able to)

Jay
  • 21
  • 3
  • Since your groups have different `id` and `userfrom` in the data, which one should it show in the results? – Barmar Sep 28 '16 at 15:59
  • Where are `nopep` and `idd` in your results? – Barmar Sep 28 '16 at 15:59
  • Why do your results have `userto`? That's not in the query. – Barmar Sep 28 '16 at 15:59
  • Hi sorry for the confusion. I have edited the question for better understanding. Basically I want to group the table by post_id and noti_type @barmar – Jay Sep 28 '16 at 16:49
  • You still haven't answered my question. Which `userto` and `userfrom` from the group should it show in the results? – Barmar Sep 28 '16 at 17:25
  • Maybe what you need to do is this: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 – Barmar Sep 28 '16 at 17:25
  • @Barmar no we dont want to show userto and from in the results, and i have also tried below query "SELECT MAX( id ) AS idd, noti_type FROM tbl_notification WHERE userto = '2' GROUP BY post_id,noti_type;" and got the results. thanks a lot for your help. – Jay Sep 29 '16 at 06:21

0 Answers0