I am selecting some values from Mysql database. I need the row numbers in the select query itself for some manipulation of these values in my java program. How can I get the row numbers in the select query itself?
For example, my result for the query is as below.
ID Name Marks
110 XXX 100
111 YYY 95
I am trying to get the output as,
ID Name Marks Student_Count
110 XXX 100 1
111 YYY 95 2
I have a SQL query like below. But it is not working.
SET @cnt := 1
SELECT ID, Name, Marks, @cnt + 1 FROM Students
How can I modify the above query to get the count in the SELECT query itself?
You should (as a best practice for this case) put some sort of ORDER BY clause
SELECT ID, Name, Marks , @cnt := @cnt + 1 AS counter FROM Students JOIN (SELECT @cnt := 0) AS tmp