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

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 - 18:56:40 CST