Find the Second Highest Value

Here’s another interesting SQL question. This problem can be easily solved by using Analytic Functions, or programming capabilities. The challenge is to solve this question with a simple SQL SELECT statement.

Given the following Numbers table. As you can see, this table has one column (named number) with values in the range of 10 to 29. Create a query to display the second highest value in that table (in our case 27).

CREATE TABLE numbers
(number int)

INSERT INTO numbers
VALUES (10)
INSERT INTO numbers
VALUES (12)
INSERT INTO numbers
VALUES (16)
INSERT INTO numbers
VALUES (18)
INSERT INTO numbers
VALUES (19)
INSERT INTO numbers
VALUES (27)
INSERT INTO numbers
VALUES (29)

SELECT MAX(number)
FROM numbers
WHERE number < (SELECT max(number) FROM numbers)