RE: Numbering rows in SQL result set

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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.