Take the 2-minute tour ×
User     Region   Points   Last_Updated        
1        5        0        1-Nov-09        I want this row
2        1        5        30-Oct-09        
3        1        2        02-Nov-09        
2        2        7        02-Nov-09        and this row
3        2        5        02-Nov-09        

Using a mysql database, I want to grab all of the point leaders for each region from region X to region Y. In the event that the points are tied, I would like to use the last_updated as a tie breaker. I figured out a way to do this but it involved several queries on the same table and joining them together. I can't help but think there is a better solution to this. Any ideas.

share|improve this question
Thanks Greg for the edit!! –  Codezy Nov 9 '09 at 19:41

1 Answer 1

up vote 4 down vote accepted

This should work:

     MyUnnamedTable T1
     T2.Region = T1.Region AND
          T2.Points > T1.Points OR
          (T2.Points = T1.Points AND T2.Last_Updated > T1.Last_Updated)
     T2.User IS NULL AND
     T1.Region BETWEEN x AND y

You're basically saying, "Give me all of the users for which there is no other user who is in the same region and either has more points or the same points with a later updated date."

share|improve this answer
Yes, worked great, thanks! –  Codezy Nov 9 '09 at 19:51
It always amazes me how good some people can be at SQL; and I don't think I'm that bad either! Currently trying to do something similar and this is going to be a big help. –  Dave Jan 12 '10 at 10:05

Your Answer


