SQL Exercise: Matches, 2nd highest stoppage time in the 2nd half

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

11. From the following table, write a SQL query to find the teams played the match where second highest stoppage time had been added in second half of play. Return match_no, play_date, stop2_sec.

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 match numbers, play dates, and stop2_sec values from the 'match_mast' table
-- where the difference in counts of distinct stop2_sec values greater than the current stop2_sec is equal to 1.

SELECT match_no, play_date, stop2_sec
-- Selects the 'match_no', 'play_date', and 'stop2_sec' columns.
FROM match_mast a
-- 'match_mast' is the name of the table being queried, using alias 'a'.
WHERE (2 - 1) = (
-- The WHERE clause checks if the difference between 2 and 1 is equal to the count of distinct stop2_sec values greater than the current stop2_sec in the subquery.
    SELECT COUNT(DISTINCT(b.stop2_sec))
    -- The subquery counts distinct stop2_sec values from the 'match_mast' table, using alias 'b'.
    FROM match_mast b
    -- 'match_mast' is the name of the table involved in the subquery, using alias 'b'.
    WHERE b.stop2_sec > a.stop2_sec
    -- Further filters rows in the subquery where stop2_sec is greater than the stop2_sec of the outer query.
);

Sample Output:

 match_no | play_date  | stop2_sec
----------+------------+-----------
       15 | 2016-06-16 |       374
(1 row)

Code Explanation:

The said query in SQL that selects the match number, play date, and stop2_sec values from the match_mast table.
The WHERE clause of the outer query compares the value of (2-1) to a subquery that counts the number of distinct stop2_sec values in the match_mast table aliased as "b" that are greater than the stop2_sec value in the current row of the outer query ("a.stop2_sec").
The subquery is executed for each row in the match_mast table, comparing the stop2_sec value in the current row of the outer query to all other stop2_sec values in the table, counting the number of distinct values that are greater.
If the count of distinct stop2_sec values that are greater than the stop2_sec value in the current row of the outer query is equal to (2-1) = 1, the row is returned in the result set.
In other words, the query is selecting the matches where the stop2_sec value is the second lowest in the match_mast table.

Alternative Solutions:

Using Subquery with Correlation:


SELECT match_no, play_date, stop2_sec
FROM match_mast a
WHERE (
    SELECT COUNT(DISTINCT b.stop2_sec)
    FROM match_mast b
    WHERE b.stop2_sec > a.stop2_sec
) = 1;

Explanation:

This query uses a correlated subquery to count the number of distinct stop2_sec values greater than the stop2_sec value of each row in the main query. It then compares this count to 1.

Using Subquery with JOIN:


SELECT a.match_no, a.play_date, a.stop2_sec
FROM match_mast a
JOIN (
    SELECT DISTINCT stop2_sec
    FROM match_mast
) b ON a.stop2_sec < b.stop2_sec
GROUP BY a.match_no, a.play_date, a.stop2_sec
HAVING COUNT(*) = 1;

Explanation:

This query uses a subquery with a join to create a list of distinct stop2_sec values. It then joins this list with the main table and groups the results. The HAVING clause ensures that only rows with a count of 1 are selected.

Using Self Join:


SELECT a.match_no, a.play_date, a.stop2_sec
FROM match_mast a
LEFT JOIN match_mast b ON a.stop2_sec < b.stop2_sec
GROUP BY a.match_no, a.play_date, a.stop2_sec
HAVING COUNT(b.stop2_sec) = 1;

Explanation:

This query uses a self-join on the match_mast table, where a.stop2_sec is less than b.stop2_sec. It then groups the results and uses the HAVING clause to select only rows where there is exactly one b.stop2_sec.

Go to:

PREV : Teams, 2nd highest stoppage time added in the 2nd half.
NEXT : The team Portugal defeated in the EURO cup 2016 final.

Practice Online