mysql - Increment value is not coming order wise, when GROUP BY clause uses in select query in Mysql

Question

SEQ_NO value is not coming order wise when I use Group by caluse or join a table. SEQ_NO should come order wise even I use ORDER BY clause for another column.

set @S = 0; 
SELECT (@S:=@S+1) AS SEQ_NO, abc, def 
from table 
group by abc 
order by abc

标签： mysql

Answer 1

Since MySQL 8.0 you can use ROW_NUMBER:

SELECT ROW_NUMBER() OVER (ORDER BY numValue) AS SEQ_NO, abc, def
FROM table_name
GROUP BY abc
ORDER BY abc

demo on dbfiddle.uk

In case you are using MySQL earlier 8.0 you have to use a sub-query:

SELECT (@S:=@S+1) AS SEQ_NO, t.* 
FROM (
  SELECT abc, def
  FROM table_name
  GROUP BY abc
  ORDER BY abc
) t, (SELECT @S:=0) sn

demo on dbfiddle.uk

You also GROUP BY abc but also using def column in result. The def column is a nonaggregated column in this case so the query isn't valid if ONLY_FULL_GROUP_BY is enabled.