Sql query for top 10 records
Can anyone help me for a simple SQL query?
This is the scenario: I need to find runners who have taken part in other competitions. Competitions were held in different cities and different specialties. The following table shows what I mean:
Runner: John White
-----------------------------------------
| NY | Rome | Madrid | Los Angeles |
------------------------------------------------
| 100m | x | | x | |
------------------------------------------------
| 200m | | x | | x |
------------------------------------------------
| 400m | | | | |
------------------------------------------------
In a few words, I have a database with the following fields: name, city, specification, and I need to find a runner who has filled more slots, a runner who has participated in more different competitions. In the "spec" field, the data is 100 m or 200 m or 300 m, if he took part, respectively, for each specialty In this example, the result is John White → 4
So how can I make a request to list the top ten competitors who have competed in the most competitions? I need a table showing the name of the runner and the number of competitions in which he took part.
Thanks in advance for your help. If you need more information, just ask.
Bye, Mat
UPDATE: In a few words, I would like to know a list of the top 10 names that contain the maximum number of different combinations of city spec fields. I think this is the best logical way to explain my answer.
Here's a sample database:
name city spec
-----------------------
john NY 100m
john Rome 200m
john Madrid 100m
john Los Ang 200m
mike Rome 200m
mike Rome 100m
The "Top 10 List" I am looking for will be:
john 4
mike 2
I hope everything is okay now. Bye!
source to share
You may be looking for something like this:
select name, count(distinct city) as attended
from competitions
group by name
order by 2 desc
limit 10;
, , (100 200 ). , , , . , , , .
: , OP 10 . , :
select name, count(name) as attended
from competitions
group by name
order by 2 desc
limit 10;
4 - 12 , 4 .