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

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

9. From the following table, write a SQL query to find the second-highest stoppage time in the second half.

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 the maximum value of 'stop2_sec' from the 'match_mast' table
-- where 'stop2_sec' is not equal to the maximum 'stop2_sec' in the entire 'match_mast' table.

SELECT MAX(stop2_sec) 
-- Selects the maximum value of 'stop2_sec'.
FROM match_mast
-- 'match_mast' is the name of the table being queried.
WHERE stop2_sec <> (
-- The WHERE clause filters rows where 'stop2_sec' is not equal to the maximum 'stop2_sec' in the subquery.
    SELECT MAX(stop2_sec) 
    -- This subquery calculates the maximum 'stop2_sec'.
    FROM match_mast
);

Sample Output:

 max
-----
 374
(1 row)

Code Explanation:

This SQL query retrieves the maximum value of "stop2_sec" from the "match_mast" table, excluding the maximum value of "stop2_sec" in the table.
By the specifies condition the "stop2_sec" value should not be equal to the maximum value of "stop2_sec" in the 'match_mast' table.
The subquery in the parentheses retrieves the maximum value of "stop2_sec" from the 'match_mast' table.
Therefore, the query will return the maximum "stop2_sec" value from the 'match_mast' table, excluding the maximum value.

Alternative Solution:

Using a Subquery and NOT IN:


SELECT MAX(stop2_sec)
FROM match_mast
WHERE stop2_sec NOT IN (
    SELECT MAX(stop2_sec)
    FROM match_mast
)

Explanation:

This query uses a subquery to find the maximum stop2_sec and then selects the maximum stop2_sec that is not equal to it in the main query.

Go to:

PREV : Player scores last goal for Portugal against Hungary.
NEXT : Teams, 2nd highest stoppage time added in the 2nd half.

Practice Online

Sample Database: soccer

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.