Re: How to number rows in SQL?
> But, you won't be able to order by your colA, colB.
You can do this without procedures or sequences, actually, with either:
1) Create an index on the column(s) you want sorted (if you don't already
have one), and include a dummy condition and/or optimiser directive to make
it select via that index.
2) Use "SELECT ... ORDER BY ... INTO TEMP" first, then reselect into the
created temp table.
Regards,
Doug Lawry
www.douglawry.webhop.org
"Richard Harnden" <richard.harnden@lineone.net> wrote in message
news:40864acc$0$95313$65c69314@mercury.nildram.net...
>
> "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
I doubt that will work on a multithreaded system. It sure won't work on XPS -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab