Private numbers for inquiry at informix

Question

I am using informix database, I need a query that you can also generate a line number along with the query

how

select row_number(),firstName,lastName from students; row_number() firstName lastName 1 john mathew 2 ricky pointing 3 sachin tendulkar 

Here is firstName, lastName from the database, where the line number is generated in the 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; 

Answer 2

You may not be able to use a ROWID in a table that is fragmented across multiple DBS spaces, so any solution using a ROWID is not particularly portable. It was also very discouraged.

If you do not have a SERIAL column in your source table (this is the best way to implement this as a general concept), look at CREATE SEQUENCE , which is more or less equivalent to the Orrible function, which generates unique numbers when SELECTed from (as opposed to SERIAL, which generates unique number when the string is INSERTED).

Answer 3

For a Table3 table with 3 columns:

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

Note: seq is a field in a table that has unique values ​​in ascending order. The numbers do not have to be contiguous.

Here is a query to return rownumber (RowNum) along with the 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; 

Answer 4

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

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

PS this is an accepted answer Expert exchange .