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.

Smut

It is courteous to select an answer - or if nothing is answering your question, it is sensible to edit your question so it can be understood. You should be aiming to select a best answer - please. – Riker

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;

Van

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).

Nabalas

a note: SERIAL in ifx (at least) 9, 10, and 11 is not unique by default. It will wrap around back to 1. – Van

Answer 3

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;

Wartburg

The formatting here leaves something to be desired. To get constant-width example material, indent stuff like the 'colnum name datatype' line indented by 4 blanks. Leave plain text indented less. – Riker

Answer 4

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.

Monoxide

Can't see the 'accepted answer' without a subscription - which, even if free, is at least a nuisance. – Riker

Answer 5

select sum(1) over (order by rowid) as row_number, M.* from systables M

Martsen

Answer 6

I know its an old question, but since i just faced this problem and got a soultion not mentioned here, i tough i could share it, so here it is:

1- You need to create a FUNCTION that return numbers in a given range:

CREATE FUNCTION fnc_numbers_in_range (pMinNumber INT, pMaxNumber INT)
RETURNING INT as NUMERO;
DEFINE numero INT;
LET numero = 0;
FOR numero = pMinNumber TO pMaxNumber   
    RETURN numero WITH RESUME;  
END FOR;    
END FUNCTION; 

2- You Cross the results of this Function with the table you want:

SELECT * FROM TABLE (fnc_numbers_in_range(0,10000)), my_table;

The only thing is that you must know before-hand the number of rows you want, you may get this with the COUNT(*) Function.

This works with my Informix Database, other implementations may need some tweaking.

Elephantine

Answer 7

Using OLAP expressions you need the OVER() with something in it, since you don't want partitions include a SORT clause, like this:

SELECT ROW_NUMBER() OVER(ORDER BY lastName, firstName) AS rn, firstName, lastName 

FROM students;

and if you don't want to order by name, you could use the way records were entered in the system by ordering by ROWID.

Maggard

It works only in 11.7 and above – Agglomeration