By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,262 Members | 2,664 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,262 IT Pros & Developers. It's quick & easy.

Need to break tie in ranking using date

P: 3
Hello there,

I am fairly new to custom SQL statements in Access, so I'll do my best to communicate my request.

I am being tasked with ranking people based on a set score. The tie breaker would be their hire date, oldest date would receive the better rank. Higher the score, better the rank. with the code I found on a Microsoft support page, ID's 13 and 14 would be ranked 2 as a tie, instead I want the oldest date to tie break:

ID Score EAD Rank
12 96 1/1/1999 1
13 78 1/2/2001 2
14 78 3/1/2002 3
15 65 4/1/1998 4

(Select Count(*) from q_Ranking_Formula Where [Score] > [Rank1].[Score]+1) AS Rank
FROM q_Ranking_Formula AS Rank1
ORDER BY Rank1.Score DESC , Rank1.EAD;

Any suggestions? A huge thanks in advance.

Ben
Jan 25 '12 #1

✓ answered by Rabbit

It should be
Expand|Select|Wrap|Line Numbers
  1. Where Score > Rank1.Score Or
  2. (Score = Rank1.Score And EAD > Rank1.EAD)

Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,359
Put in an additional condition in your where clause that also compares the dates for when the scores are the same. Also, I'm pretty sure that +1 is supposed to be outside of the subquery.
Jan 25 '12 #2

ADezii
Expert 5K+
P: 8,627
Not sure as far as the SQL goes, but this is a relatively simple matter to resolve using Recordset processing. Simply add a Field named [Rank]{LONG}, then execute the following Code. It always allows for a 3rd Tie Breaker (earliest [ID]) should [Score] and [EAD] be equal.
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstRanking As DAO.Recordset
  3. Dim strSQL As String
  4. Dim lngRank As Integer
  5.  
  6. strSQL = "SELECT [Score],[EAD],[ID],[Rank] FROM q_Ranking_Formula " & _
  7.          "ORDER BY [Score] DESC,[EAD] ASC, [ID] ASC;"
  8.  
  9. Set MyDB = CurrentDb
  10. Set rstRanking = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
  11.  
  12. With rstRanking
  13.   Do While Not .EOF
  14.     lngRank = lngRank + 1
  15.       .Edit
  16.         ![Rank] = lngRank
  17.       .Update
  18.         .MoveNext
  19.   Loop
  20. End With
  21.  
  22. rstRanking.Close
  23. Set rstRanking = Nothing
  24.  
Sample Data:
Expand|Select|Wrap|Line Numbers
  1. ID   Score    EAD        Rank
  2. 1    96    10/12/2002     4
  3. 2    96    1/1/1996       2
  4. 3    96    1/19/1999      3
  5. 3    97    6/8/2009       1
  6. 4    78    1/2/2001       6
  7. 5    78    3/1/2002      10
  8. 6    65    4/1/1998      11
  9. 7    78    3/30/2001      8
  10. 8    78    3/30/2001      9
  11. 9    78    3/17/2001      7
  12. 10   79    8/17/2006      5
  13.  
Results ([Rank] Ascending):
Expand|Select|Wrap|Line Numbers
  1. ID   Score    EAD        Rank
  2. 3    97    6/8/2009       1
  3. 2    96    1/1/1996       2
  4. 3    96    1/19/1999      3
  5. 1    96    10/12/2002     4
  6. 10   79    8/17/2006      5
  7. 4    78    1/2/2001       6
  8. 9    78    3/17/2001      7
  9. 7    78    3/30/2001      8
  10. 8    78    3/30/2001      9
  11. 5    78    3/1/2002      10
  12. 6    65    4/1/1998      11
  13.  
Jan 25 '12 #3

P: 3
Thanks for the replies guys. I was hoping to attempt this without code, so I'll keep ADezii's suggestion in my back pocket.

Thanks for the tip on the +1 Rabbit!

I know why this doesn't work, however, I'm not sure how to add a condition to the Where clause to check for a duplicate rank before checking the date:
Expand|Select|Wrap|Line Numbers
  1. Rank: (Select Count(*) from q_Ranking_Formula Where [Score] > [Rank1].[Score] and [EAD] > [Rank1].[EAD])+1 
Any suggestions?
Jan 25 '12 #4

Rabbit
Expert Mod 10K+
P: 12,359
It should be
Expand|Select|Wrap|Line Numbers
  1. Where Score > Rank1.Score Or
  2. (Score = Rank1.Score And EAD > Rank1.EAD)
Jan 25 '12 #5

P: 3
Holy crow, it worked! The only modification I made was to sort the rank for when the tie was issued by changing the greater than to a less than between the EAD and Rank1.EAD.

Thanks again Rabbit!
Jan 25 '12 #6

Rabbit
Expert Mod 10K+
P: 12,359
No problem, good luck.
Jan 26 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.