MySQL get latest record checking two columns

Question

I have results like this

I need to get the latest race of each jockey (First getting the max date of the Race, and then the Max Race number). The results should look something like this:

Note: The database is very huge. The query should be very fast.

Don't forget to put an index on the Jockey column if you use it frequently to retrieve data. — Ananta, Jan 19 '18 at 15:12
For efficient "groupwise max", including getting more than one: http://mysql.rjweb.org/doc.php/groupwise_max — Rick James, Jan 19 '18 at 16:49

Geoffrey Wiseman · Accepted Answer · 2018-01-19T15:38:35.707

2

When you say "Max Race number", do you mean the number of the latest race (race with the most recent date), or the highest number?

Assuming you mean the former, and making up the name 'Races' for your table:

SELECT 
    Race.*
FROM 
    (
        SELECT Jockey, MAX(Date) as MaxDate 
        FROM Races GROUP BY Jockey
    ) MaxRaceDate 
    inner join (
        SELECT Jockey, Date, MAX(Race) as MaxRace
        FROM Races 
        GROUP BY Jockey, Date
    ) MaxRaceForDate 
        on MaxRateDate.Jockey = MaxRaceForDate.Jockey
            and MaxRateDate.MaxDate = MaxRaceForDate.Date
    inner join Race
        on Race.Jockey=MaxRace.Jockey 
            and Race.Date = MaxRace.MaxDate
            and Race.Race = MaxRaceForDate.MaxRace

This query is getting long, so it's possible that I've got an error, but it should be roughly right. ;)

Basically, you need an aggregate query on the table to find the date, then another aggregate query for the maximum rate for the date, then finally the table itself to get the information. And then you need to put them together with either subqueries or joins, and joins are typically much faster, so I went that way.

edited Jan 19 '18 at 15:38

answered Jan 19 '18 at 15:20

Geoffrey Wiseman

5,459
3
34
52

Question has been edited and commented to suggest that the former interpretation is correct. – Geoffrey Wiseman Jan 19 '18 at 15:22
A jockey can ride on two races on the same date, so I want to check the race number also – Dragon Warrior Jan 19 '18 at 15:30
Yes, the above query will give the results, but it will take quite long to get the results, is there any other way to get the results quickly? – Dragon Warrior Jan 19 '18 at 15:48
You'd probably have to profile the query, see what's taking the time. Adding indexes on jockey, race and date would be an obvious first start. If you can't change the database, then I'm not sure there's much you're going to be able to do to speed things up. – Geoffrey Wiseman Jan 19 '18 at 16:56

score 1 · Answer 2 · answered Jan 19 '18 at 15:10

1

I believe, you looking for:

SELECT Jockey,MAX(DATE),MAX(RACE) FROM myTable  GROUP BY Jockey

answered Jan 19 '18 at 15:10

apomene

14,282
9
46
72

You misunderstood, I reupload the image again, first max should be date and then max should be race number – Dragon Warrior Jan 19 '18 at 15:16

MySQL get latest record checking two columns

2 Answers2

Linked