Let’s say that you are given a SQL table called “Compare” (the schema is shown below) with only one column called “Numbers”.

Compare  
{
     Numbers INT(4)
 }

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

This problem is difficult because you are forced to think outside the box, and use whatever SQL you know to solve a problem without using the most obvious solution (doing a “select MAX…” from the table).

Probably the best way to start breaking this problem down is by creating a sample table with some actual data that matches the schema given. Here is a sample table to start out with:

The value that we want to extract from the table above is 90, since it is the maximum value in the table. How can we extract this value from the table in a creative way (it will have to be creative since we can’t use the max or min aggregates)? Well, what are the properties of the highest number (90 in our example)? We could say that there are no numbers larger than 90 – that doesn’t sound very promising in terms of solving this problem.

We could also say that 90 is the only number that does not have a number that is greater than it. If we can somehow return every value that does not have a value greater than it then we would only be returning 90. This would solve the problem. So, we should try to design a SQL statement that would return every number that does not have another number greater than it. Sounds fun right?

Let’s start out simple by figuring out which numbers do have any numbers greater than themselves. This is an easier query. We can start by joining the Compare table with itself, which will create all the possible pairs for which each value in one column is greater than the corresponding value in the other column. This is what the following query does:

SELECT Smaller.Numbers, Larger.Numbers
FROM Compare as Larger, Compare AS Smaller
WHERE Smaller.Numbers < Larger.Numbers

Now, let's use the sample table we created, and we end up with this table after running the query above:

Now we have every value in the Smaller column except the largest value of 90. So, all we have to do is find the value that is not in the Smaller column (but is in the Compare table), and that will give us the maximum value. Here's what the query would look like:

SELECT Numbers FROM Compare WHERE Numbers NOT IN
{ SELECT Smaller.Numbers FROM COMPARE AS Larger, 
Compare AS Smaller
WHERE Smaller.Numbers < Larger.Numbers}

This will give us what we want - the maximum value. But there is one small bug - if the maximum value is repeated in the Compare table then it will return that value twice. We can prevent that by simply using the DISTINCT keyword. So, here's what the query looks like now:

SELECT DISTINCT Numbers FROM Compare WHERE Numbers NOT IN
{ SELECT Smaller.Numbers FROM COMPARE AS Larger, 
Compare AS Smaller
WHERE Smaller.Numbers < Larger.Numbers}