SQL Exercise: Find which was the highest audience match
SQL soccer Database: Subqueries Exercise-3 with Solution
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
10 | G | 2016-06-14 | WIN | N | 0-2 | 20004 | 70005 | 55408 | 160244 | 63 | 189
11 | G | 2016-06-14 | WIN | N | 0-2 | 20001 | 70018 | 34424 | 160197 | 61 | 305
12 | G | 2016-06-15 | DRAW | N | 1-1 | 20009 | 70004 | 38742 | 160320 | 15 | 284
13 | G | 2016-06-15 | WIN | N | 1-2 | 20003 | 70001 | 38989 | 160405 | 62 | 189
14 | G | 2016-06-15 | DRAW | N | 1-1 | 20007 | 70015 | 43576 | 160477 | 74 | 206
15 | G | 2016-06-16 | WIN | N | 2-0 | 20005 | 70013 | 63670 | 160154 | 71 | 374
16 | G | 2016-06-16 | WIN | N | 2-1 | 20002 | 70003 | 34033 | 160540 | 62 | 212
17 | G | 2016-06-16 | WIN | N | 0-2 | 20004 | 70016 | 51043 | 160262 | 7 | 411
18 | G | 2016-06-17 | DRAW | N | 0-0 | 20008 | 70008 | 73648 | 160165 | 6 | 208
19 | G | 2016-06-17 | WIN | N | 1-0 | 20010 | 70007 | 29600 | 160248 | 2 | 264
20 | G | 2016-06-17 | DRAW | N | 2-2 | 20009 | 70005 | 38376 | 160086 | 71 | 280
21 | G | 2016-06-18 | WIN | N | 3-0 | 20006 | 70010 | 33409 | 160429 | 84 | 120
22 | G | 2016-06-18 | WIN | N | 3-0 | 20001 | 70004 | 39493 | 160064 | 11 | 180
23 | G | 2016-06-18 | DRAW | N | 1-1 | 20005 | 70015 | 60842 | 160230 | 61 | 280
24 | G | 2016-06-19 | DRAW | N | 0-0 | 20007 | 70011 | 44291 | 160314 | 3 | 200
25 | G | 2016-06-20 | WIN | N | 0-1 | 20004 | 70016 | 49752 | 160005 | 125 | 328
26 | G | 2016-06-20 | DRAW | N | 0-0 | 20003 | 70001 | 45616 | 160463 | 60 | 122
27 | G | 2016-06-21 | WIN | N | 0-3 | 20010 | 70006 | 28840 | 160544 | 62 | 119
28 | G | 2016-06-21 | DRAW | N | 0-0 | 20009 | 70012 | 39051 | 160392 | 62 | 301
29 | G | 2016-06-21 | WIN | N | 0-1 | 20005 | 70017 | 58874 | 160520 | 29 | 244
30 | G | 2016-06-21 | WIN | N | 0-1 | 20007 | 70018 | 44125 | 160177 | 21 | 195
31 | G | 2016-06-22 | WIN | N | 0-2 | 20002 | 70013 | 32836 | 160504 | 60 | 300
32 | G | 2016-06-22 | WIN | N | 2-1 | 20001 | 70008 | 37245 | 160085 | 70 | 282
33 | G | 2016-06-22 | WIN | N | 2-1 | 20008 | 70009 | 68714 | 160220 | 7 | 244
34 | G | 2016-06-22 | DRAW | N | 3-3 | 20004 | 70002 | 55514 | 160322 | 70 | 185
35 | G | 2016-06-23 | WIN | N | 0-1 | 20003 | 70014 | 44268 | 160333 | 79 | 221
36 | G | 2016-06-23 | WIN | N | 0-1 | 20006 | 70003 | 34011 | 160062 | 63 | 195
37 | R | 2016-06-25 | WIN | P | 1-1 | 20009 | 70005 | 38842 | 160476 | 126 | 243
38 | R | 2016-06-25 | WIN | N | 1-0 | 20007 | 70002 | 44342 | 160547 | 5 | 245
39 | R | 2016-06-26 | WIN | N | 0-1 | 20002 | 70012 | 33523 | 160316 | 61 | 198
40 | R | 2016-06-26 | WIN | N | 2-1 | 20004 | 70011 | 56279 | 160160 | 238 | 203
41 | R | 2016-06-26 | WIN | N | 3-0 | 20003 | 70009 | 44312 | 160173 | 62 | 124
42 | R | 2016-06-27 | WIN | N | 0-4 | 20010 | 70010 | 28921 | 160062 | 3 | 133
43 | R | 2016-06-27 | WIN | N | 2-0 | 20008 | 70004 | 76165 | 160235 | 63 | 243
44 | R | 2016-06-28 | WIN | N | 1-2 | 20006 | 70001 | 33901 | 160217 | 5 | 199
45 | Q | 2016-07-01 | WIN | P | 1-1 | 20005 | 70003 | 62940 | 160316 | 58 | 181
46 | Q | 2016-07-02 | WIN | N | 3-1 | 20003 | 70001 | 45936 | 160550 | 14 | 182
47 | Q | 2016-07-03 | WIN | P | 1-1 | 20001 | 70007 | 38764 | 160163 | 63 | 181
48 | Q | 2016-07-04 | WIN | N | 5-2 | 20008 | 70008 | 76833 | 160159 | 16 | 125
49 | S | 2016-07-07 | WIN | N | 2-0 | 20004 | 70006 | 55679 | 160322 | 2 | 181
50 | S | 2016-07-08 | WIN | N | 2-0 | 20005 | 70011 | 64078 | 160160 | 126 | 275
51 | F | 2016-07-11 | WIN | N | 1-0 | 20008 | 70005 | 75868 | 160307 | 161 | 181
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.
Practice Online
Sample Database: soccer
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Find the winner of EURO cup 2016.
Next SQL Exercise: Find the match in which Germany played against Poland.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics