putting serial numbers to the rows retrieved

Hi Everyone, I am in need of a SQL in which I need to get rows from a table but it should have a column with serial numbers starting from 1 to as many number of rows. To explain well, if I am getting 10 rows from a sql, a column, let us say, serial_no, I should get which should have values starting from 1 to 10. As below: Serial_No Column_1 Column_2 1 xxxxx xxxxxx 2 xxxxx xxxxxx 3 xxxxx xxxxxx - ----- ------ - ----- ------ 10 xxxxx xxxxxx Please help Thanks in advace. Infman.

This is the easiest way I can think of. Create a temp table with all columns you want, and an additional field call serial_no as follows create temp table my_report ( serial_no serial not null, column_1, column_2 .. ) with no log ; your select statement should be insert into my_report select 0, column_1, column_2, ... from .. where ... select * from my_report order by serial_no It will automatically by numbered the way you want it. "infman" <sunil.guduru@gmail.com> wrote in message news:1103329855.724486.273310@f14g2000cwb.googlegroups.com... > Hi Everyone, > > I am in need of a SQL in which I need to get rows from a table but it > should have a column with serial numbers starting from 1 to as many > number of rows. > > To explain well, if I am getting 10 rows from a sql, a column, let us > say, serial_no, I should get which should have values starting from 1 > to 10. > > As below: > > Serial_No Column_1 Column_2 > > 1 xxxxx xxxxxx > 2 xxxxx xxxxxx > 3 xxxxx xxxxxx > - ----- ------ > - ----- ------ > 10 xxxxx xxxxxx > Please help > > Thanks in advace. > > Infman. >

rkusenet wrote: > This is the easiest way I can think of. > > Create a temp table with all columns you want, and an additional field > call serial_no as follows > > create temp table my_report > ( serial_no serial not null, > column_1, > column_2 > .. > ) with no log ;> > your select statement should be > > insert into my_report > select 0, > column_1, > column_2, > ... > from .. > where ...> > select * from my_report order by serial_no> > It will automatically by numbered the way you want it. If the SELECT statement, which cannot contain an ORDER BY clause, happens to generate the rows in the correct sequence... > "infman" <sunil.guduru@gmail.com> wrote: >>I am in need of a SQL in which I need to get rows from a table but it >>should have a column with serial numbers starting from 1 to as many >>number of rows. >> >>To explain well, if I am getting 10 rows from a sql, a column, let us >>say, serial_no, I should get which should have values starting from 1 >>to 10. >> >>As below: >> >>Serial_No Column_1 Column_2 >> >>1 xxxxx xxxxxx >>2 xxxxx xxxxxx >>3 xxxxx xxxxxx >>- ----- ------ >>- ----- ------ >>10 xxxxx xxxxxx In Oracle, this is achieved by ROWNUM. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/