Re: finding ordinal position in a result set?

From: ddf <oratune_at_msn.com>
Date: Wed, 22 Jul 2009 12:01:57 -0700 (PDT)
Message-ID: <f84e64be-5c9c-4a09-a272-0513987543c5_at_33g2000vbe.googlegroups.com>

On Jul 22, 5:11 am, m..._at_pixar.com wrote:
> 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
>
> Many TIA!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios

Knowing that if some record is inserted which is assigned a seq value before good old fred's Thomas' suggestion is a good one:

SQL> create table ordinals(

  2          seq     number,
  3          name    varchar2(40)

  4 );

Table created.

SQL>
SQL> insert all
  2 into ordinals
  3 values(2,'bob')
  4 into ordinals
  5 values(5,'able')
  6 into ordinals
  7 values(8,'fred')
  8 into ordinals
  9 values(9,'steve')
 10 select * from dual;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select seq, name, row_number() over (order by seq)   2 from ordinals
  3 order by seq;

       SEQ NAME                                     ROW_NUMBER()OVER

(ORDERBYSEQ)

---------- ----------------------------------------
----------------------------
         2
bob                                                                 1
         5
able                                                                2
         8
fred                                                                3
<---- good old fred, right where he should be
         9
steve                                                               4

SQL>
SQL> select seq, name
  2 from
  3 (select seq, name, row_number() over (order by seq) rn   4 from ordinals)
  5 where rn = 3;

       SEQ NAME

---------- ----------------------------------------
         8 fred

SQL>
SQL> truncate table ordinals;

Table truncated.

SQL>
SQL> insert all
  2 into ordinals
  3 values(5,'able')
  4 into ordinals
  5 values(8,'fred')
  6 into ordinals
  7 values(2,'bob') <--- inserted after fred but the sequence number didn't change
  8 into ordinals
  9 values(9,'steve')
 10 select * from dual;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select seq, name
  2 from
  3 (select seq, name, row_number() over (order by seq) rn   4 from ordinals)
  5 where rn = 3;

       SEQ NAME

---------- ----------------------------------------
         8 fred

SQL>
SQL> truncate table ordinals;

Table truncated.

SQL>
SQL> insert all
  2 into ordinals
  3 values(5,'able')
  4 into ordinals
  5 values(8,'fred')
  6 into ordinals
  7 values(2,'bob')
  8 into ordinals
  9 values(9,'steve')
 10 into ordinals
 11 values(7,'waldo')
 12 into ordinals
 13 values(6,'clive') <--- oops, clive cut in line before fred  14 into ordinals
 15 values(17,'stan')
 16 into ordinals
 17 values(14,'archie')
 18 select * from dual;

8 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select seq, name
  2 from
  3 (select seq, name, row_number() over (order by seq) rn   4 from ordinals)
  5 where rn = 3;

       SEQ NAME

---------- ----------------------------------------
         6 clive

SQL> David Fitzjarrell Received on Wed Jul 22 2009 - 14:01:57 CDT