0

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
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
saveesh p m
  • 33
  • 10
  • Please frame SQL questions with **relevant and minimal sample data** showcasing your requirements, and **corresponding expected output**. Please read this link: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) – Madhur Bhaiya Aug 22 '19 at 11:08
  • Yes, the MySQL manual does say that using variables in the same script that define/alter them isn't a good idea. What nasty hack are you trying to achieve that's making you do this? – Caius Jard Aug 22 '19 at 11:11
  • 1
    (My first recommendation would be that you upgrade to MySQL 8 and use ROW_NUMBER()) – Caius Jard Aug 22 '19 at 11:12
  • Everyone get over to the dupe and upvote [this answer](https://stackoverflow.com/a/46753800/2757035), which mentions that you can do this natively in MySQL now rather than hacking it in. – underscore_d Aug 22 '19 at 11:20

1 Answers1

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.

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87