Aditya Krishnakumar Aditya Krishnakumar - 2 months ago 6
MySQL Question

selecting single record if multiple records are having same length

I am trying to solve this problem on HackerRank

I tried this query :

select city, length(city) from station
where length(city) = (select max(length(city)) from station)
or length(city) = (select min(length(city)) from station)
order by
length(city) ASC,city ASC;


After running the above query, I get the following result :

Amo 3
Lee 3
Roy 3
Marine On Saint Croix 21


My Problem is : I only want to select
Amo
&
Marine On Saint Croix
. Not others.
How to achieve this ?

Thanks in Advance

Answer

Here is the correct query that works:

 (SELECT city, LENGTH(city) AS length FROM station
ORDER BY LENGTH(city), city
LIMIT 1) UNION 

(SELECT city, LENGTH(city) AS length FROM station
ORDER BY LENGTH(city) DESC, city
LIMIT 1)