Please pardon the caps!
ALTER TABLE YOUR_TABLE
ADD (RANK NUMBER(3));
OR IF YOU ALREADY HAVE A RANK ... THEN
UPDATE YOUR TABLE
SET RANK=NULL;
COMMIT;
- RUN THE FOLLOWING SCRIPT.
DECLARE CURSOR C1
IS SELECT 'X' FROM YOUR_TABLE
WHERE RANK IS NULL;
ANY_LEFT CHAR(1);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO ANY_LEFT;
EXIT WHEN C1%NOTFOUND;
UPDATE YOUR_TABLE
SET RANK=(SELECT MAX(NVL(RANK,0)+1) FROM YOUR_TABLE)
WHERE PCT=(SELECT MAX(PCT) FROM YOUR_TABLE
WHERE RANK IS NULL);
END LOOP;
COMMIT;
END;
"bel inconnu" <reply_at_newsgroup.com> wrote in message
news: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 Mon Feb 05 2001 - 19:50:58 CST
