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.

Compare  


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.
Subscribe to our newsletter for more free interview questions.
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.
SELECT Numbers FROM Compare WHERE Numbers NOT IN ( SELECT Smaller.Numbers FROM Compare AS Larger JOIN Compare AS Smaller ON Smaller.Numbers < Larger.Numbers ) 
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:
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 ) 
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.