How to number rows in SQL?
Jean Sagi, on IDS 9.3, asked how to number rows of a result set in SQL. Richard Harnden suggested a temp table with a SERIAL column loaded from the select, though ordering by columns would not work, and preferred a stored procedure. Jay Aymond posted a stored procedure with a global counter, called as seq_counter(1) to reset and seq_counter(null) to increment.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Has anyone tryed to number the result set of a sql in dbaccess for example?
I mean:
select <number> no, colA, colB, etc
from some table
What I want to be returned is :
no colA colB etc
-- ---- ---- ----
1 data data data
2 data data data
3 data data data
4 data data data
5 data data data
6 data data data
.
.
.
I'm using IDS 9.3
I know I could what I explained with a stored procedure, but I wonder if there is a magical function or trick that could be used to achieve what I want in sql.
Any help would be very appreciated.
Chucho!
Jean Sagi
jeansagi@myrealbox.com
jeansagi@netscape.net
sending to informix-list
"Jean Sagi" <jeansagi@myrealbox.com> wrote in message
news:c64cc8$egj$1@terabinaries.xmission.com...
>
>
> Has anyone tryed to number the result set of a sql in dbaccess for
example?
>
> I mean:
>
> select <number> no, colA, colB, etc
> from some table
>
> What I want to be returned is :
>
> no colA colB etc
> -- ---- ---- ----
> 1 data data data
> 2 data data data
> 3 data data data
> 4 data data data
> 5 data data data
> 6 data data data
> .
You'll have to use a tempory table, with a serial column and insert zeros;
something like ...
CREATE TEMP TABLE xyzzy
(
id SERIAL,
tabid INTEGER
) WITH NO LOG;
INSERT INTO xyzzy
SELECT 0, tabid
FROM systables;
SELECT *
FROM xyzzy;
But, you won't be able to order by your colA, colB.
>
> I'm using IDS 9.3
>
> I know I could what I explained with a stored procedure, but I wonder if
there is a magical function or trick that could be used to achieve what I
want in sql.
A stored procedure is probably a better solution.
--
rh
A Stored Procedure is your best bet.
"Jean Sagi" <jeansagi@myrealbox.com> wrote in message
news:c64cc8$egj$1@terabinaries.xmission.com...
>
>
> Has anyone tryed to number the result set of a sql in dbaccess for
example?
>
> I mean:
>
> select <number> no, colA, colB, etc
> from some table
>
> What I want to be returned is :
>
> no colA colB etc
> -- ---- ---- ----
> 1 data data data
> 2 data data data
> 3 data data data
> 4 data data data
> 5 data data data
> 6 data data data
> .
> .
> .
>
> I'm using IDS 9.3
>
> I know I could what I explained with a stored procedure, but I wonder if
there is a magical function or trick that could be used to achieve what I
want in sql.
>
> Any help would be very appreciated.
>
>
> Chucho!
>
>
>
>
> Jean Sagi
> jeansagi@myrealbox.com
> jeansagi@netscape.net
>
>
> sending to informix-list
How about something like this:
create procedure seq_counter (in_seq_count integer) returning integer;
define global seq_counter integer default 0;
if in_seq_count is not null
then
let seq_counter = in_seq_count;
end if
let seq_counter = seq_counter + 1;
return seq_counter;
end procedure;
Example:
select abc, seq_counter(1)
from systables
where tabid = 99;
select abc, seq_counter(null)
from systables
where tabid = 99;
"Jean Sagi" <jeansagi@myrealbox.com> wrote in message
news:c64cc8$egj$1@terabinaries.xmission.com...
>
>
> Has anyone tryed to number the result set of a sql in dbaccess for
example?
>
> I mean:
>
> select <number> no, colA, colB, etc
> from some table
>
> What I want to be returned is :
>
> no colA colB etc
> -- ---- ---- ----
> 1 data data data
> 2 data data data
> 3 data data data
> 4 data data data
> 5 data data data
> 6 data data data
> .
> .
> .
>
> I'm using IDS 9.3
>
> I know I could what I explained with a stored procedure, but I wonder if
there is a magical function or trick that could be used to achieve what I
want in sql.
>
> Any help would be very appreciated.
>
>
> Chucho!
>
>
>
>
> Jean Sagi
> jeansagi@myrealbox.com
> jeansagi@netscape.net
>
>
> sending to informix-list