Write a SQL query to maximum Zipcode from a table without using MAX or MIN aggregate functions.

Consider Zipcode_list table with column Zipcode
ZIPCODE
7500
7525
7550
7600
7575

Answer: Point to be noted is that the Maximum zipcode is not smaller than any Zipcode in the list.

We can use self join to find the list of Zipcodes that are smaller than at least one other Zipcode. Once we get that list, we just use NOT IN to find the Zipcode from Zipcode_list that does not exist in this smaller list. That will be the maximum Zipcode with no Zipcode bigger than it.

Query will be as follows:

SELECT DISTINCT Zipcode
FROM Zipcode_list
WHERE Zipcode NOT IN (
SELECT Smaller_list.Zipcode
FROM Zipcode_list AS Larger_list
JOIN Zipcode_list AS Smaller_list
ON Smaller_list.Zipcode < Largerlist.Zipcode
)

Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*