need help for my sql statement

[Mayo123]

Good morning, everyone!
I have a sql statement
SELECT TOP 20 WITH TIES ssn, LName, FInitial, SUM(hrsWorked) AS [Total HrsWorked], SUM(amtEarned) AS [Total AmtEarned]
FROM MyDetailedTable
GROUP BY ssn, LName, FInitial
Order by SUM(hrsWorked) desc, SUM(amtEarned) desc

I need to have a field called Rank to be inserted into this select statement which will have the number running from 1 to 20 each time the statement is executed.

How can I implement this?

Thanks in advance

 

[vongrunt]

SQL2000 or 2005?

Also: what kind of rank? Vanilla, dense or simple row enumeration (1, 2, 3...)?

And WITH TIES may return more than 20 rows...

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Mayo123]

Sorry about confusing.
I'm using sql 2000
The reason I need a rank is on my form to show who's ranked No 1,2,3 etc...It should be a simple row enumeration (1, 2, 3...)when running the statement.
Thanks!

 

[vongrunt]

Arguably the simplest way is with identity table:

select identity(tinyint, 1, 1) as rank, X.*
into #tmp
from
(	<your query with TOP and ORDER BY here>
) X

select * from #tmp
drop table #tmp

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[Mayo123]

Thanks, it worked