MySQL Find Top 3 Ranked Teams

Question

I need to display listing on my ranking page in the following manner

Team_Name      Played       Won      Lost     Points     In_Top_3
Some Team      5            5        0        11          Yes

Points are calculated as

2 Points / match winning

+1 if the match won was a quarter final

+2 if the match won was a semi-final

+3 if the match won was a Grand-finale

I was successful to achieve most of them but one that is In_Top_3, although there is a cheap workaround to add a $count and check it for <=3 and display Yes under the column but is there any way I can do it with an SQL query.

My current SQL query is as follows

SELECT
    T.id, T.name, T.status, IFNULL(T.image, 'no-image.png') AS DP,
    (SELECT COUNT(*)
    FROM badminton_matches MT
    WHERE (MT.team_one = T.id OR MT.team_two = T.id)) 
  AS played,
    (SELECT COUNT(*)
    FROM badminton_match_results R
    WHERE R.winner_id = T.id) AS won,
    (SELECT COUNT(*)
    FROM badminton_matches MT JOIN badminton_match_results MR
        ON (MR.match_id = MT.id)
    WHERE (MT.team_one = T.id OR MT.team_two = T.id) AND MR.winner_id != T.id) AS lost,
    (
   ((SELECT COUNT(*)
    FROM badminton_match_results R
    WHERE R.winner_id = T.id) * 2) 
     + 
   ((SELECT COUNT(*)
    FROM badminton_match_results R JOIN badminton_matches M ON (M.id = R.match_id AND M.match_type = 'quarter')
    WHERE R.winner_id = T.id))
  ) AS Points
FROM badminton_teams T
ORDER BY (Points) DESC

how can i flag top 3 ranked Teams along with my current query, when I have the following table structure, and

Table : teams

+------------+----------------------------+------+-----+-------------------+----------------+
| Field      | Type                       | Null | Key | Default           | Extra          |
+------------+----------------------------+------+-----+-------------------+----------------+
| id         | int(11)                    | NO   | PRI | NULL              | auto_increment |
| name       | varchar(150)               | NO   |     | NULL              |                |
| image      | text                       | YES  |     | NULL              |                |
| status     | enum('active','in-active') | NO   |     | active            |                |
| added_on   | datetime                   | NO   |     | CURRENT_TIMESTAMP |                |
| updated_on | datetime                   | YES  |     | NULL              |                |
+------------+----------------------------+------+-----+-------------------+----------------+

Table : matches

+------------+---------------------------------------+------+-----+-------------------+----------------+
| Field      | Type                                  | Null | Key | Default           | Extra          |
+------------+---------------------------------------+------+-----+-------------------+----------------+
| id         | int(11)                               | NO   | PRI | NULL              | auto_increment |
| team_one   | int(11)                               | NO   | MUL | NULL              |                |
| team_two   | int(11)                               | NO   |     | NULL              |                |
| added_on   | datetime                              | NO   |     | CURRENT_TIMESTAMP |                |
| match_type | enum('pool','quarter','semi','final') | NO   |     | pool              |                |
| sets       | smallint(2)                           | NO   |     | 1                 |                |
+------------+---------------------------------------+------+-----+-------------------+----------------+

Table : Match Results

+-----------+----------+------+-----+-------------------+----------------+
| Field     | Type     | Null | Key | Default           | Extra          |
+-----------+----------+------+-----+-------------------+----------------+
| id        | int(11)  | NO   | PRI | NULL              | auto_increment |
| match_id  | int(11)  | NO   | MUL | NULL              |                |
| winner_id | int(11)  | NO   | MUL | NULL              |                |
| added_on  | datetime | NO   |     | CURRENT_TIMESTAMP |                |
+-----------+----------+------+-----+-------------------+----------------+

Using a counter in your application code is probably the best solution. But if you have to much time, you can search for "mysql+rank". — Paul Spiegel, Oct 15 '16 at 16:24

score 1 · Accepted Answer · edited Sep 03 '18 at 23:38

There is a similar question here:

How to add ROW INDEX as a column to SQL SELECT query?

Extending from that question you want something like:

SET @row_num = 0;
SELECT
    T.id, T.name, T.status, IFNULL(T.image, 'no-image.png') AS DP,
    (SELECT COUNT(*)
    FROM badminton_matches MT
    WHERE (MT.team_one = T.id OR MT.team_two = T.id)) 
      AS played,
    (SELECT COUNT(*)
    FROM badminton_match_results R
    WHERE R.winner_id = T.id) AS won,
    (SELECT COUNT(*)
    FROM badminton_matches MT JOIN badminton_match_results MR
        ON (MR.match_id = MT.id)
    WHERE (MT.team_one = T.id OR MT.team_two = T.id) AND MR.winner_id != T.id) AS lost,
    (
       ((SELECT COUNT(*)
    FROM badminton_match_results R
    WHERE R.winner_id = T.id) * 2) 
         + 
       ((SELECT COUNT(*)
    FROM badminton_match_results R JOIN badminton_matches M ON (M.id = R.match_id AND M.match_type = 'quarter')
    WHERE R.winner_id = T.id))
      ) AS Points,

    /* here is the magic */
    (@row_num
:= @row_num + 1) < 4 AS row_index

    FROM badminton_teams T
    ORDER BY
(Points) DESC

This will add an extra column called row_index where 1 means in top 3 and 0 means not in the top 3.

Remember, that you must call the SET before each SELECT and within the same session.

thanks, @David Newcomb that was an old thread and I had it solved already but let's select it as an answer as I used the same approach mentioned by you. — Muhammad Omer Aslam, Jan 30 '18 at 08:52

MySQL Find Top 3 Ranked Teams

1 Answers1