with mysql can I have a column generated with an autoincrement value

Question

Possible Duplicate:
MySql Row Number?

I'd like to number the result of a query. Can I do that with mysql?

Let's say I have the following query

SELECT id
       , application_id
       , 3rd_column_to_generate From SemesterApplication 
WHERE SemesterApplication.semester.id = 1 
ORDER BY SemesterApplication.modified

I need the query to return a 3rd column that will hold the number of the result. If the query return 20 results, that 3rd column will return value between 1 and 20.

This query will be used as a sub-query in another query.

Thank you for pointing out the duplicate entry – kaklon Sep 18 '11 at 20:19 — kaklon, Sep 18 '11 at 20:19

score 0 · Accepted Answer · answered Sep 18 '11 at 20:02

There's not nice way to do this in mysql. The sort of universal hack is to create a variable and increment it. So:

SELECT   @rownum:=@rownum+1 rownum, 
         id,
         application_id,
         3rd_column_to_generate 
From     SemesterApplication,
         (select @rownum:=0) r
WHERE    SemesterApplication.semester.id = 1 
ORDER BY SemesterApplication.modified

with mysql can I have a column generated with an autoincrement value

1 Answers1