# Re: finding ordinal position in a result set?

From: ddf <oratune_at_msn.com>
Date: Wed, 22 Jul 2009 12:01:57 -0700 (PDT)

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

Original text of this message