SQL Exercise: Find which was the highest audience match

Last update on January 16 2026 12:32:33 (UTC/GMT +8 hours)

3. From the following table, write a SQL query to find the highest audience match. Return match_no, play_stage, goal_score, audience.

Sample table: match_mast

 match_no | play_stage | play_date  | results | decided_by | goal_score | venue_id | referee_id | audence | plr_of_match | stop1_sec | stop2_sec
----------+------------+------------+---------+------------+------------+----------+------------+---------+--------------+-----------+-----------
        1 | G          | 2016-06-11 | WIN     | N          | 2-1        |    20008 |      70007 |   75113 |       160154 |       131 |       242
        2 | G          | 2016-06-11 | WIN     | N          | 0-1        |    20002 |      70012 |   33805 |       160476 |        61 |       182
        3 | G          | 2016-06-11 | WIN     | N          | 2-1        |    20001 |      70017 |   37831 |       160540 |        64 |       268
        4 | G          | 2016-06-12 | DRAW    | N          | 1-1        |    20005 |      70011 |   62343 |       160128 |         0 |       185
        5 | G          | 2016-06-12 | WIN     | N          | 0-1        |    20007 |      70006 |   43842 |       160084 |       125 |       325
        6 | G          | 2016-06-12 | WIN     | N          | 1-0        |    20006 |      70014 |   33742 |       160291 |         2 |       246
        7 | G          | 2016-06-13 | WIN     | N          | 2-0        |    20003 |      70002 |   43035 |       160176 |        89 |       188
        8 | G          | 2016-06-13 | WIN     | N          | 1-0        |    20010 |      70009 |   29400 |       160429 |       360 |       182
        9 | G          | 2016-06-13 | DRAW    | N          | 1-1        |    20008 |      70010 |   73419 |       160335 |        67 |       194
.........
       51 | F          | 2016-07-11 | WIN     | N          | 1-0        |    20008 |      70005 |   75868 |       160307 |       161 |       181

View the table

Sample Solution:

SQL Code:

-- This SQL query retrieves specific columns from the 'match_mast' table
-- for rows where the audience count matches the maximum audience count in the entire 'match_mast' table.

SELECT match_no, play_stage, goal_score, audence 
-- Selects the specified columns ('match_no', 'play_stage', 'goal_score', 'audence') from 'match_mast'.
FROM match_mast 
-- 'match_mast' is the name of the table being queried.
WHERE audence = (
-- The WHERE clause filters rows where the 'audence' column matches the maximum audience count in the subquery.
    SELECT max(audence) 
    -- The subquery calculates the maximum audience count in the 'match_mast' table.
    FROM match_mast
);

Sample Output:

 match_no | play_stage | goal_score | audence
----------+------------+------------+---------
       48 | Q          | 5-2        |   76833
(1 row)

Code Explanation:

The said query in SQL that selects the match number, play stage, goal score, and audience from the 'match_mast' table where the audience is equal to the maximum audience from the same table.
The WHERE clause filter the results to only include rows where the "audience" column is equal to the maximum value of the "audience".

Alternative Solutions:

Using ORDER BY with LIMIT:


SELECT match_no, play_stage, goal_score, audence
FROM match_mast
ORDER BY audence DESC
LIMIT 1;

Explanation:

This query uses ORDER BY to sort the rows in descending order of audence and then uses LIMIT to select only the top row with the highest audience.

Using JOIN with a Subquery:


SELECT m1.match_no, m1.play_stage, m1.goal_score, m1.audence
FROM match_mast m1
JOIN (
    SELECT MAX(audence) AS max_audence
    FROM match_mast
) m2 ON m1.audence = m2.max_audence;

Explanation:

This query uses a subquery to find the maximum audience and then performs a join to select the rows where the audience matches this maximum value.

Go to:

PREV : Find the winner of EURO cup 2016.
NEXT : Find the match in which Germany played against Poland.

Practice Online