Removing duplicate by using GROUP BY Not working

Question

SELECT 
    m.dept_no, m.emp_no, d.dept_name
FROM
    dept_manager_dup m
        JOIN
    departments_dup d ON m.dept_no = d.dept_no
GROUP BY m.emp_no
ORDER BY dept_no;

I used the above SQL statement but I got a query interrupted, it doesn't show me the error details ( shows me the query completed and the result for those that display some kind of list or shows me the query interrupted if there is something wrong without any details of showing rows affected, or detail errors).

But when I remove the GROUP BY part it successfully executes the query and displays the list, I get the interrupted message when I add the GROUP BY to avoid the duplicates

Can you please help me fix the problem?

Answer 1

Hi Samrawit!
Thanks for reaching out!
Actually, without the GROUP BY clause, the result set will be the same. The reason for the error message is that you have used the GROUP BY clause without any aggregate function. Different versions of MySQL may treat this in a different way. So, in this case you can simply remove the clause.Alternatively, the issue is likely related to the SQL mode ONLY_FULL_GROUP_BY, which enforces strict SQL standards. In this mode, every column in the SELECT list must be either in the GROUP BY clause or used in an aggregate function.
You can resolve it by changing the SQL mode to exclude ONLY_FULL_GROUP_BY.
For example:

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

Hope this helps.
Best,
Martin

Removing duplicate by using GROUP BY Not working

Submit an answer

related questions