RE: Numbering rows in SQL result set

[R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>]

not that this helps right now, but what you want is a sequence.

They are available in DB2 UDB for LUW, but not in iSeries until i5/OS
V5R3.


create sequence mine start with 1;

alter sequence mine restart;
select next value for mine, t.name from table t;

that would give you the results you are looking for


On Wed, 2004-05-05 at 14:13, Vern Hamberg wrote:
> This is allowed in V5R2 and up. But I found that it did not honor sorting 
> in descending order in the "with ... as" clause. So
> 
> (select name greatones order by name desc)
> 
> still resulted in ascending order. I thought for a minute that this made 
> sense, but it doesn't anymore.
> 
> If you put your subject into google, you will come up with a few other 
> solutions. One involved a table stored procedure - is that like UDTF's?
> 
> V5R2 also has identity columns, but I don't think they can be used in a 
> correlated result set like this.
> 
> HTH
> Vern
> 
> At 11:45 AM 5/5/2004, you wrote:
> >This would work....
> >
> >with TmpTable as
> >         (select name greatones )
> >select rrn(TmpTable), name
> >from TmpTable
> >
> >
> >Only problem would be if you wanted to use an "order by name" it would need
> >to in the TmpTable select and I'm not positive that that is allowed.
> >
> >May want to look in the archives for other ideas, I seem to remember this
> >coming up before.
> >
> >HTH,
> >Charles
> >
> > > -----Original Message-----
> > > From: meovino@xxxxxxxxxxxxxxxxx [mailto:meovino@xxxxxxxxxxxxxxxxx]
> > > Sent: Wednesday, May 05, 2004 10:32 AM
> > > To: Midrange Systems Technical Discussion
> > > Subject: Numbering rows in SQL result set
> > >
> > >
> > > Is there an SQL scalar function or some other trick I can use
> > > to add a
> > > column to a result set that numbers the rows in the set.
> > > Let's say my
> > > result set currently is:
> > >
> > > John
> > > Paul
> > > George
> > > Ringo
> > >
> > > I want it to look like:
> > >
> > > 1   John
> > > 2   Paul
> > > 3   George
> > > 4   Ringo
> > >
> > > Thanks!
> > >
> > > Mike E.
> 
> 
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
-- 
"Bigamy is having one wife too many. Monogamy is the same."
-- Oscar Wilde