5 Replies Latest reply on Jun 4, 2007 9:34 PM by BKBK

    Ranking Query return

    spacehog Level 1
      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.