row number as column value?

[preaney]

I have a view (MS Access Query, actually) that has two columns: ticker and volume. The results are ordered by volume descending. What I want to do is add a "rank" column, where rank is the number of tickers with volume greater than the current one.

have this:

Ticker Volume
QQQ 1000
CSCO 900


need this:

Ticker Volume Rank
QQQ 1000 1
CSCO 900 2

Short of taking the query data and populating a new table with an autonumber field, I can't figure out how to do that. Can it be done in SQL? Any help would be appreciated.

Thanks,
pat

 

[rac2]

Seems like I saw the answer to a similar question in one of the SQL forums recently. The closest I found in a search was this thread thread183-57871 in the MS SQL Server forum. That one seems to be using a temp table, but it suggests the following which does not. I wonder what the relative performance is for this query versus a stored procedure with temp table approach?

select Ticker,  Volume,
    (select Count(*) from volume_view n where n.Volume <= a.Volume) AS &quot;Rank&quot; 
from volume_view a

order by Volume

 

[preaney]

Thanks, it worked. The query was pretty slow, though. I may try the temp table to see if that speeds things up.