I have in the code two selects, first randomly selecting any 20 sportsmen
db.Query("SELECT sp_no, first_name FROM sportsmen LIMIT ?,20", rand.Intn(100000))
Then for every returned sportsmen in a loop do another select from points table where published their results in different competitions
db.Query("SELECT max(point) FROM points WHERE sp_no =" + spNo)
In result I have 20 sportsmen with their first_name and highest points numbers. I'm looking for optimisation of these selects to combine them into one for avoiding looping in a code and then measure performance.
How to have one select statement which returning first_name and their highest points?
Looks like it's combination compound selects, join and exists verification?