Re: How can I assign a rank to records in a resultset?

In ORACLE 8.0.5 I'd use subqueries with
rownum pseudocolumn,
"distinct" opertor and "minus" sign to
invert sortin order:

select s.state, a.rank from states s,
(select distinct rownum rank, pct from
(select distinct -pct pct from states))
a
where s.pct=-a.pct order by rank, state;

--
A.M. Andreyev,
ORACLE DBA
"bel inconnu" <reply_at_newsgroup.com>
wrote in message
news:95nirt$5ua$1_at_slb7.atl.mindspring.ne
t...


> I have simplified my problem for the

 purpose of this question, but basically


> here is the situation. Let's say we

 are wanting to rank states according to


> their literacy rates. We have two

 columns in a table, the state name


> and the percentage of literacy.

>

> select distinct

>    state,

>    pct

> from

>    states

> order by

>    state

>

> AK   99.5

> AL   97.5

> CA   97.8

> GA   98.2

> IL   98.2

> VA   98.0

>

> I am basically wanting to calculate a

 rank column for each record in the


> resultset, based on the percentage

 value relative to the other


> records. Notice that I also want

 states with an identical percentage


> (ex. GA and IL) to be assigned an

 equal ranking.


>

> AK   99.5    1

> AL   97.5    5

> CA   97.8    4

> GA   98.2    2

> IL   98.2    2

> VA   98.0    3

>

>  My current plan of attack is to

 create another view where


> the state records are distinct by

 percantage and sorted. That view would


> then be

> joined to the first on the percentage

 column. In that case, all I need to do


> is find a way to assign

> a sequential number to each record in

 the distinct view. However, I can not


> figure out

> how to do even that.

>

> 99.5    1

> 98.2    2

> 98.0    3

> 97.8    4

> 97.5    5

>

> ???

>

> I'm sure there is a simply way to

implement this, however, my mind is
blank.


> I would

> prefer to find a solution that can be

 implemented within a view and not rely


> on a

> temporary table or any client side

 coding.


>

>

>

>

>