Codezy Codezy - 1 year ago 65
SQL Question

Mysql: Query to find row with max with tie breaker

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 Source

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."

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download