If you have Oracle 8.1.6 this is an example
of using the dense_rank() analytic function
which is now built in to SQL.
--
Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
Practical Oracle 8i: Building Efficient Databases
Publishers: Addison-Wesley
Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
bel inconnu wrote in message <95nirt$5ua$1_at_slb7.atl.mindspring.net>...
>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.
>
>
>
>
>
Received on Tue Feb 06 2001 - 13:32:25 CST
