Let’s say that you are given a SQL table called “Compare” (the schema is shown below) with only one column called “Numbers”.
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 – this is called a self join, which you can read more about here in case you are not familiar with self joins: Example of self join in SQL .
Using a self join, we can create all the possible pairs for which each value in one column is greater than the corresponding value in the other column. This is exactly what the following query does:
SELECT Smaller.Numbers, Larger.Numbers FROM Compare as Larger JOIN Compare AS Smaller ON 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. This means that 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. We can easily do this using the NOT IN operator in SQL.
But before we do that we have to change the query above so that it only selects the "Smaller" column - because that is the only column we are interested in. So, we can simply change our query above to this in order to get the "Smaller" column:
SELECT Smaller.Numbers FROM Compare as Larger JOIN Compare AS Smaller ON Smaller.Numbers < Larger.Numbers
Now, all we have to do is apply the NOT IN operator to find the max value.
This will give us what we want - the maximum value. But there is one small problem with the SQL above - 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:
And there we have our final answer. Of course, some of you may be saying that there is a much simpler solution to this problem. And you would be correct. Here is a simpler answer to the problem using the SQL Top clause along with the SQL Order By clause - this is what it would look like in SQL Server:
select TOP 1 -- select the very top entry in result set Numbers from Compare order by Numbers DESC
And since MySQL does not have a TOP clause this is what it would look like in MySQL using just ORDER BY and LIMIT :
select Numbers from Compare order by Numbers DESC - order in descending order LIMIT 1 --retrieve only one value
So, even though there are a couple of much simpler answers it is nice to know the more complicated answer using a self join so that you can impress your interviewer with your knowledge.