Aditya Krishnakumar Aditya Krishnakumar - 1 year ago 71
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 Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download