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

I think the easiest way would be to use the following code and adjust its return accordingly. SELECT rowid, * FROM table

It works for me but please note that it will return the row number in the database, not the row number in the query.

P.S. it's an accepted answer from Experts Exchange.

Answer 2

You may not be able to use ROWID in a table that's fragmented across multiple DBSpaces, so any solution that uses ROWID is not particularly portable. It's also strongly discouraged.

If you don't have a SERIAL column in your source table (which is a better way of implementing this as a general concept), have a look at CREATE SEQUENCE, which is more or less the equivalent of an Orrible function that generates unique numbers when SELECTed from (as opposed to SERIAL, which generates the unique number when the row is INSERTed).

Answer 3

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;

Answer 4

Given a table called Table3 with 3 columns:

colnum  name   datatype
======= =====  ===
1       no     text;
2       seq    number;
3       nm     text;

NOTE: seq is a field within the Table that has unique values in ascending order. The numbers do not have to be contiguous.

Here is query to return a rownumber (RowNum) along with query result

SELECT table3.no, table3.seq, Table3.nm,
      (SELECT COUNT(*) FROM Table3 AS Temp
         WHERE Temp.seq < Table3.seq) + 1 AS RowNum
    FROM Table3;