-1

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?

Ramesh
  • 765
  • 7
  • 24
  • 52
  • Define 'not working'. – GolezTrol Dec 20 '13 at 19:08
  • See: http://stackoverflow.com/questions/1895110/row-number-in-mysql – Brendan Dec 20 '13 at 19:10
  • possible duplicate of [With MySQL, how can I generate a column containing the record index in a table?](http://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table) – GolezTrol Dec 20 '13 at 19:10

1 Answers1

0

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
AgRizzo
  • 5,261
  • 1
  • 13
  • 28