i have a table / I have a table: id_msg - int (255) autoincrement id_user - varchar (255)
chat_id -varchar (255)
msg - text
ava_img - varchar (500)
first_name- varchar (500)
time_of_msg -timestamp [CURRENT_TIMESTAMP]

I need to get all the lines (with the output of all the fields) but without repeating the value of the chat_id field, when using group by I get an error about non-aggregating data

SELECT * FROM in_msg GROUP BY chat_id HAVING count(DISTINCT chat_id) > 1
  • And why do you choose * i. all table fields? you need to decide which fields you really need and what values ​​to show for fields not participating in the group by, i.e. apply max / min / sum functions to them - Mike
  • It does not happen. What does chat_id mean without repeating? If you have the same chat_id but with different id_user - what do you expect to see at the output? - plesser
  • one
    And if you do gorup by chat_id, then you will have exactly 1 chat_id in each line of the result and therefore the condition having count (distinct chat_id)> 1 will always be false - Mike
  • one
    for example, I have 8 entries in the table, in which the chat ID is repeated 3 times, i.e. I need to get 3 lines with all the columns - tim
  • one
    great, you have one line with chat_id = 1 and msg = "ABC" And the second line is chat_id = 1 and msg = "XYZ". At the output, you want to get one chat_id line = 1, but what should be reflected in the msg field? - Mike

1 answer 1

 select * from table a inner join ( select chat_id ,count(*) from table group by chat_id having count(*) > 1 ) b on a.chat_id = b.chat_id