CODEWITHSUNDEEP
sqlmysql
Codezy
Codezy

Reputation: 5570

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.

Upvotes: 2

Views: 3526

Answers (2)

DDD
DDD

Reputation: 1

Here is another option:

SELECT T1.[User]
FROM MyUnnamedTable T1 INNER JOIN (select MAX(points*100000000+convert(varchar(8),last_updated,112)) as score, region
                                           from MyUnnamedTable
                                           group by region) t2 ON T2.Region = T1.Region AND (t1.points*100000000+convert(varchar(8),t1.last_updated,112))=score
WHERE T1.Region BETWEEN x AND y

In the nested query, we select the max of a combination of points and last_update. By encoding points in the most significant digits, we give points higher priority. When there is a tie, then lower digits containing last_update will break the tie.

Other tiebreaking methods in SQL are discussed in http://sqlmag.com/t-sql/t-sql-tiebreakers

Upvotes: 0

Tom H
Tom H

Reputation: 47472

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

Upvotes: 6

Related Questions