Select rows with highest value at least as good as lowest value; select name with higher than at least two other name

I have four tables look like this:

Athlete | id|name| |---|----| |1 |Mark| |2 |John| |3 |Bob | |4 |Dan | |5 |James| |6|Mike| |7|David|

Performance |aid|score| |---|-----| |1 |3.8 | |1 |4.9 | |1 |5.2 | |2 |4.7 | |2 |4.8 | |3 |2.6 | |3 |2.8 | |3 |3.2 |

PersonalRecord |aid|record| |---|------| |1|3.5| |2|2.8| |3|4.7| |4|4.5| |5|3.2| |6|2.9| |7|6.7|

I have four tables look like this. Athlete_Country |aid|country| |---|-------| |1|US| |2|US| |3|US| |4|Canada| |5|Canada| |6|Canada| |7|Canada| I want to select athlete names whose best performance is at least as good as someone else's worst performance. I also want to list athlete names whose personal record is better than at least two other atheletes' personal records from the same country. I am thinking on using ORDER BY and GROUP BY in the same queries. Thanks in advance!

https://stackoverflow.com/questions/66897198/select-rows-with-highest-value-at-least-as-good-as-lowest-value-select-name-wit April 01, 2021 at 09:08AM