Ranking Query return

[spacehog]

This sounds like it should be simple. I want to be able to rank data in a query return. Currently I have:

SELECT salesID, sales
FROM allmembers
ORDER BY sales DESC
<cfset salesRank = 0>
<cfoutput>
<cfset salesRank = #salesRank# + 1>
<cfquery>
INSERT INTO rankings(salesID,salesRank)
VALUES ('#salesID#',#salesRank#)
</cfquery>

This places the seller's ID and his/her rank into the ranking table. My question is how can I handle ties? If two or more people have the same sales value I would like them all to have the same rank. Then the person after them would have his/her correct ranking.

example
John has a ranking of 1. Bob, Chris and Tom all tied and would have rankings not of 2 but of 4. Then the next person after them would have a ranking of 5 (unless there was another tie.

How can I code in to look for ties and to rank correctly. It is similar to how golfers in tournaments are ranked.

[Dan_Bracuk]

Some db's have a rank function. To see if yours does, google "yourdb rank".

[BKBK]

> John has a ranking of 1. Bob, Chris and Tom all tied and would have rankings
> not of 2 but of 4. Then the next person after them would have a ranking of 5
> (unless there was another tie.

The ranking I know of puts John in 1st, then Bob, Chris and Tom in joint 2nd and the next person in 5th place.

[BKBK · Accepted solution]

An example that determines rank according to my previous post:

[spacehog]

BKBK

Thanks

Works great

[BKBK]

!