Re: Problem re. ordering selection

In article <2rc972$hca_at_bigmig.auslig.gov.au>, parnell_at_auslig.gov.au (Malcolm Parnell) writes:
> There exists a table X with columns and data thus :
>
> name num
> ---- ---
> a 2
> b 10
> c 4
> d 7
> e 1
> f 9
> g 7
>
> I want to know what ranking "f" get if the table is ordered by num, name
> ie.
>
> e 1 --> rank 1
> a 2 --> rank 2
> c 4 --> rank 3
> d 7 --> rank 4
> g 7 --> rank 5
> f 9 --> rank 6
> b 10 --> rank 7
>
> How can I do this in a SQL statement?
>
> I've tried :
>
> select rownum,name,num
> from x
> order by num,name
>
> but the rownum is assigned BEFORE the order is performed.
>
> I can't use an order by clause when inserting into another table ...
> and I can't figure how I can use a sequence.

If you put an index on NUM,NAME then you can remove the order by clause and use a WHERE NUM > 0 AND NAME > ' ' provided NUM is numeric otherwise enclose the 0 in quotes as a string.

The WHERE clause forces use of the index. Set your > values to suite the type of data your holding.

This will walk the index and return the entries in RANKING order smallest to largest and you can use ROWNUM to give the RANKING value.

I haven't tried this but it should work.

There is probably a way to do this with nested selects but I like the index method as it removes a sort from the query.

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************