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!

+3


source to share


4 answers


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 .

+5




? , , , ? , . , .

SELECT COUNT(*)
FROM city_runner
WHERE runnerid = ? #The runner id? You gave so little for me to work with...

      



, , ...

0




, . ,

city|name|spec

:

SELECT TOP 10 name, total_races
FROM (
  SELECT COUNT(*) as total_races, name
  FROM thattable
  GROUP BY name
) as t1
ORDER BY total_races DESC

      

-, , ,

: . , name

runner

,

0




. n-

    SELECT TOP  nth_largest_no * FROM Products Order by price desc

      

  SELECT TOP  10 * FROM Products Order by price desc;

      

0









All Articles