If your getting EROOR CODE 1055: Try This Code Instead!
SELECT
m.dept_no, m.emp_no, d.dept_name
FROM
dept_manager_dup m
JOIN
department_dup d ON m.dept_no = d.dept_no
GROUP BY m.dept_no, m.emp_no, d.dept_name
ORDER BY m.dept_no;
3 answers ( 0 marked as helpful)
Hi Rafi!
Thanks for reaching out.
Thanks for sharing this piece of information with the Community!
Best,
Tsvetelin
Is the above the correct approach?
I also ran into this error, which others have run into as well it looks like. I asked ChatGPT, and it recommended the SELECT DISTINCT as the better approach, but also said we could use GROUP BY for all the rows we are selecting, as Rafi suggested above.
ChatGPT said that older versions of SQL allowed the functoinality of GROUP BY showed in the video by the instructor, but said newer versions of SQL stopped allowing this behavior by default.
Let me know what is the best industry best practice approach here please.
Thanks!
-Justin
I also ran into this error, which others have run into as well it looks like. I asked ChatGPT, and it recommended the SELECT DISTINCT as the better approach, but also said we could use GROUP BY for all the rows we are selecting, as Rafi suggested above.
ChatGPT said that older versions of SQL allowed the functoinality of GROUP BY showed in the video by the instructor, but said newer versions of SQL stopped allowing this behavior by default.
Let me know what is the best industry best practice approach here please.
Thanks!
-Justin
Hi Justin!
Thanks for reaching out!
Here’s a quick breakdown:
1. Error 1055 (ONLY_FULL_GROUP_BY) occurs in newer versions of MySQL when your SELECT clause contains columns that are not aggregated or included in the GROUP BY clause. Older versions were more lenient, but the newer standard enforces stricter SQL compliance.
2. About the current query:
Rafi's solution avoids the error by listing all columns in the GROUP BY clause, which works — but it's not always the best option.
3. Industry best practice:
If you're not using aggregation (like COUNT(), SUM(), etc.), then GROUP BY is often not necessary. In your case, it seems you're trying to eliminate duplicates. The better practice would be:
This is clearer, more efficient, and reflects your intent to return unique combinations directly.
Hope this helps.
Best,
Tsvetelin
Thanks for reaching out!
Here’s a quick breakdown:
1. Error 1055 (ONLY_FULL_GROUP_BY) occurs in newer versions of MySQL when your SELECT clause contains columns that are not aggregated or included in the GROUP BY clause. Older versions were more lenient, but the newer standard enforces stricter SQL compliance.
2. About the current query:
Rafi's solution avoids the error by listing all columns in the GROUP BY clause, which works — but it's not always the best option.
3. Industry best practice:
If you're not using aggregation (like COUNT(), SUM(), etc.), then GROUP BY is often not necessary. In your case, it seems you're trying to eliminate duplicates. The better practice would be:
SELECT DISTINCT
m.dept_no, m.emp_no, d.dept_name
FROM
dept_manager_dup m
JOIN
department_dup d ON m.dept_no = d.dept_no
ORDER BY m.dept_no; This is clearer, more efficient, and reflects your intent to return unique combinations directly.
Hope this helps.
Best,
Tsvetelin