sql query for top 10 records

Question

Can anyone help me for a simple SQL query?

This is the scenario: i need to find the runners who took part to more competitions. Competitions have been in different cities and different specialities. The following table shows better what i mean:

Runner: John White

        -----------------------------------------
        |  NY  |  Rome |  Madrid |  Los Angeles |
 ------------------------------------------------
 | 100m |  x   |       |   x     |              |
 ------------------------------------------------
 | 200m |      |  x    |         |      x       |
 ------------------------------------------------
 | 400m |      |       |         |              |
 ------------------------------------------------

In few words i have a database with the following fields: name, city, spec and i need to find the runner who filled more slots, the runner who participated to more different competitions. In the "spec" field data are 100m or 200m or 300m if he took part respectively to each speciality In the example the result is John White -> 4

So, how can i make a query to list the 1st ten runners who participated to the major number of competitions? I need a table showing the runner name and the number of competitions he took part.

Thanks in advance for your help. If you need more infos just ask.

Bye, Mat

UPDATE: In few words i'd like to know the top 10 list of names that have the maximum number of different combinations of city-specs fields. This one i think is the best logical way to explain my answer.

Here is an example of the 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'm looking for will be the following:

john   4
mike   2

I hope that now it is all ok. Bye!

Answer 1

Perhaps you are looking for something like this:

select name, count(distinct city) as attended
  from competitions
 group by name
 order by 2 desc
 limit 10;

This query returns a competitor and number of events he participated in, excluding duplicates (100 and 200 meters at the same event). If you want to count all appearances remove distinct keyword, and in case you want to show versatility list replace city with distinct speciality. Query works by counting all rows that belong to the same name, ordering by that count descendingly and limiting output to ten rows.

UPDATE: I suppose that OP needs a list of top 10 competitors by appearance. So, each record for competitor counts as one:

select name, count(name) as attended
  from competitions
 group by name
 order by 2 desc
 limit 10;

Will give 4 for Mr. White, and 12 for anyone who attended all 4 competitions in all 3 disciplines.