How could I assign rank.

I have the below query

select [FI NAME], round (sum(FIWORKING.GROSPFT), 0)as FIGROSS from from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]
order by sum (FIWORKING."GROSPFT") DESC

Which returns below

name1 65784
name2 32586
name3 37892

based on this, I would like to be able to set a rank (aka 1 , 2 , 3, etc)
for NAME1, NAME2, etc

I would like to store this in table GROSPFTRANK

That Table looks like

name1 1
name2 2

I haven't been able to figure out the SQL to do this.

Thanks for an help

ChrisYou can create a table with an identity column. Insert into that table using your query and the ranking will automatically be applied.|||There are several ways to skin this cat.

One method is to make the second field in your destination table an incrementing identity column, and then just insert your ordered data into it.

A second method would be to create a temporary table with an autoincrement column and load your data into it prior to storing it in your permanent table.

Or you could use this sql statement, which runs two totals and then counts the number of records in the second set which are less than the value in the first set:

Insert into GROSPFTRANK ([FI NAME], [RANK])
select [FIWORKINGOUTER].[FI NAME], count([FIGROSS].[FI NAME])
from
(select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]) FIWORKINGOUTER
inner join
(select [FI NAME], round(sum(FIWORKING.GROSPFT), 0)as FIGROSS
from FIWORKING
where FIWORKING.[FI NAME] IS NOT NULL
group by FIWORKING.[FI NAME]) FIWORKINGSUB
on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)

Note that this assigns two [FI NAME] values the same rank if they have the same summary value. If you want unique ranks based on, say, the alphabetical order of [FI NAME], join the two subqueries with this ON statement:

on (FIWORKINGOUTER.FIGROSS < FIWORKINGSUB.FIGROSS)
or (FIWORKINGOUTER.FIGROSS = FIWORKINGSUB.FIGROSS
and [FIWORKINGOUTER].[FI NAME] < [FIWORKINGSUB].[FI NAME])

blindman