SQL challenge day 1

Imagine you are tasked with finding the city with shortest and longest name from a database. This may sound straightforward, but when you add conditions like ordering in case of ties, the query can become a bit more complex. In this post, we will explore how to write SQL queries to find the shortest and longest city names in a table, handle ties in alphabetical order, and combines multiple queries into one result set efficiently.

select CITY, len_city
from
(select Top 1 CITY, LEN(CITY) as len_city
from STATION
order by LEN(CITY), CITY) as shortestCity
UNION ALL
select CITY, len_city
from
(select Top 1 CITY, LEN(CITY) as len_city
from STATION
order by LEN(CITY) DESC, CITY) as longestCity;

When querying for the shortest and longest city names in SQL server, I learned that

1. Sorting and selecting with conditions. Using ORDER BY in combination with TOP 1 allows us to filter data based on length while resolving ties alphabetically.
2. Handling multiple queries. SQL server requires careful handling when combining multiple queries with UNION ALL, especially when using ORDER BY. Wrapping each query in a subquery ensures that the ordering logic applies correctly within each query block.
3. Efficiency matters. Combining queries using UNION ALL instead of writing seperate queries helps streamline the process and reduces redundancy in code.

By mastering these techniques, you can write more effective and concise SQL queries to retrieve targeted information, no matters how specific the conditions.

We’ll look more challenging queries in the future blogs.