Mysql: Query to find row with max with tie breaker

Question

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.

Answer 1

This should work:

SELECT
     T1.User
FROM
     MyUnnamedTable T1
LEFT OUTER JOIN MyUnnamedTable T2 ON
     T2.Region = T1.Region AND
     (
          T2.Points > T1.Points OR
          (T2.Points = T1.Points AND T2.Last_Updated > T1.Last_Updated)
     )
WHERE
     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."