Re: finding ordinal position in a result set?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 22 Jul 2009 06:32:57 -0700 (PDT)
Message-ID: <6671d69a-e9b7-45f3-9c24-fb162f95034d_at_c36g2000yqn.googlegroups.com>

On Jul 22, 6:19 am, Thomas Kellerer <OTPXDAJCS..._at_spammotel.com> wrote:
> m..._at_pixar.com, 22.07.2009 12:11:
>
> > Suppose I have a query like the following.
> > What's the best way to find the position of a particular item?
> > e.g. I would like to find out that:
>
> >       'fred' is position 3 of 4 total.
>
> > select seq,name from tbl order by seq;
> > seq   name
> > ---   ----
> >   2    bob
> >   5    able
> >   8    fred
> >   9    steve
>
> Use the row_number() function
>
> select seq, name, row_number() over (order by seq)
> from tbl
> order by seq;

Mark, realize that the ordinal position is relative to the data at the time the query is ran and on a rerun could potentially be different depending on your data, DML activity, and how the ordering key is assigned.

If there are no deletes and the ordering key values are not reused then the ordering should be static for you data but remember for future use that it is not always static.

HTH -- Mark D Powell -- Received on Wed Jul 22 2009 - 08:32:57 CDT