database - Row numbers for a query in informix

Question

I am using informix database, I want a query which you could also generate a row number along with the query

Like

select row_number(),firstName,lastName 
from students;

row_number() firstName lastName
1            john      mathew
2            ricky     pointing
3            sachin    tendulkar

Here firstName, lastName are from Database, where as row number is generated in a query.

Answer 1

The best way is to use a (newly initialized) sequence.

begin work;
create sequence myseq;
select myseq.nextval,s.firstName,s.lastName from students s;
drop sequence myseq;
commit work;