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

mh (AT) pixar (DOT) com, 22.07.2009 12:11:
Use the row_number() function

select seq, name, row_number() over (order by seq)
from tbl
order by seq;

On Jul 22, 6:19*am, Thomas Kellerer <OTPXDAJCS... (AT) spammotel (DOT) com>
wrote:
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 --

On Jul 22, 5:11*am, m... (AT) pixar (DOT) com wrote:
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