0

I am trying to convert old 5.6 MySQL queries to be compatible with MySQL 8. Unfortunately many of the queries contain joins with SELECT * FROM table GROUP BY column1. These cause a syntax error in the latest version of mysql. I have tried several variations of group by and distinct to try and achieve the same behavior but have been unsuccessful.

tgbrock
  • 31
  • 3
  • Can you share one of the failing querys, along with the **exact** error message? – Nico Haase Mar 09 '20 at 21:24
  • If `column1` is the primary key, it should still work. – Barmar Mar 09 '20 at 21:24
  • And if it wasn't the primary key, you probably weren't getting the expected results in the old version -- it won't get all columns from the same row in the group. – Barmar Mar 09 '20 at 21:27

1 Answers1

0

Here is one method:

select t.*
from (select t.*,
             row_number() over (partition by column1 order by column1) as seqnum
      from t
     ) t
where seqnum = 1;

This does return one extra column, but is otherwise equivalent.

If your tables have a primary key, say id, then you can also do:

select t.*
from t
where t.id = (select min(t2.id)
              from t t2
              where t2.column1 = t.column1
             );

Note: Some folks wrote code such as yours under the assumption that MySQL would return the first row for each column1 value. That is a grave misunderstanding of SQL, because SQL tables represent unordered sets so "first" is not even defined. MySQL documentation also cautions against making such an assumption.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786