SQL Practice 3

SQL Practice 3

Question

Given this table

1
2
3
CREATE TABLE Compare (
Numbers INT
)

Write a SQL query that will return the maximum value from the “Numbers” column, without using a SQL aggregate like MAX or MIN.

Answer

There are two ways to do that, first we can use a self join

1
2
3
4
5
6
7
SELECT DISTINCT Numbers
FROM Compare
WHERE Numbers NOT IN (
SELECT Smaller.Numbers
FROM Compare AS Larger
JOIN Compare AS Smaller ON Smaller.Numbers < Larger.Numbers
)

Second, which is easier, we can sort and limit the number of returned results.

1
2
3
4
SELECT Numbers
FROM Compare
ORDER BY Numbers DESC
LIMIT 1

Reference