SQL Query Question

[AcceSs-AbLe]

Hi,

I am trying to come up with a query that is able to Rank the results in Descending order. I was successful in coming up with this but the problem I have is that when two fields are equal, it ranks the result as 1,2,2,4 instead of 1,2,3,4. I need to insert a Tie-Breaker statement, how would I accomplish this? This is my current SQL Statement:

HEVolRank: (Select Count(*) from [Division Snapshot XXX] Where [HEVolVar] >[Snapshot].[HEVolVar])+1

[Snapshot]=Alias of [Division Snapshot XXX]

The tie breaker would be the HEVol field.

Is there a way to add an iif in SQL statement?

TIA

 

[Joe4]

Could you create a new calculated field which combines both fields use in determining the ranking, and then do the ranking off of this calculated field?

 

[AcceSs-AbLe]

Thanks for your reply. I am restricted from creating a new calculated field based on combination of the two fields due to sensitivity of the calculation. What would be an another approach?