How to RANK in 2000? 1

[daglugub37]

I would like to somehow assign a rank to rows in a record set.

The query is simple enough

select * from sales
order by gross desc

but how could I assign a rank to the results? Meaning the top gross would be #1, and if there are ties then to assign the same rank.

I know there is new functions in 05 to handle this but does anyone know a clever way to do this on 00?

 

[gmmastros]

In SQL 2000, you'll need to use a 'helper' table.

Something like this...

[green]-- Sample Data[/green]
Declare @Sales Table(Gross Int, SalesPerson VarChar(20))

Insert Into @Sales Values(1, 'A')
Insert Into @Sales Values(2, 'B')
Insert Into @Sales Values(3, 'C')
Insert Into @Sales Values(3, 'D')
Insert Into @Sales Values(4, 'E')

[green]-- The Query[/green]
Declare @Temp Table(RowId Integer Identity(1,1), Gross Int)

Insert Into @Temp
Select Distinct Gross
From   @Sales
Order By Gross DESC

Select T.RowId As Rank, S.*
From   @Temp T
       Inner Join @Sales S
         On T.Gross = S.Gross
Order By T.RowId

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[daglugub37]

you are awesome, thanks