CODEWITHSUNDEEP
mysql
Zeus
Zeus

Reputation: 2253

Len of Column query in SQL

I'm new to SQL thus the question.

So I've the following table with Id, City, State named Station.

And I need to Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

Can someone help me get started with this. I tried the len() function on city but it doesn't seem to work.

Upvotes: 3

Views: 5398

Answers (9)

iamdotpe
iamdotpe

Reputation: 21

This worked for me:

(SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY) ASC LIMIT 1) UNION (SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY) DESC LIMIT 1)

Upvotes: 2

CharlesL
CharlesL

Reputation: 1

SELECT City, CHAR_LENGTH(City)
FROM STATION
ORDER BY CHAR_LENGTH(City) ASC, City
LIMIT 1;

SELECT City, CHAR_LENGTH(City)
FROM STATION
ORDER BY CHAR_LENGTH(City) DESC, City
LIMIT 1;

Try the above solution I reckon should solve your problem.

Upvotes: 0

Shubham verma
Shubham verma

Reputation: 33

This may work also -

select first(CITY) ,len(first(CITY)) from STATION where CITY in (select CITY from STATION  where len(CITY) in (SELECT MAX(LEN(CITY)) FROM STATION) order by CITY asc)
UNION
select first(CITY) ,len(first(CITY)) from STATION where CITY in (select CITY from STATION  where len(CITY) in (SELECT MIN(LEN(CITY)) FROM STATION) order by CITY asc);

Upvotes: 0

Madhuri
Madhuri

Reputation: 1

(select CITY,length(CITY)
from STATION
where length(CITY)=(select min(length(CITY)) from STATION) 
order by city
LIMIT 1)
union
(select CITY,length(CITY)
from STATION
where length(CITY)=(select max(length(CITY)) from STATION) 
order by city
LIMIT 1)

Upvotes: -1

coderbhai
coderbhai

Reputation: 41

(select CITY,length(CITY)
from STATION
where length(CITY)=(select min(length(CITY)) from STATION) 
order by city
LIMIT 1)
union
(select CITY,length(CITY)
from STATION
where length(CITY)=(select max(length(CITY)) from STATION) 
order by city
LIMIT 1);

Upvotes: 0

Ahmed Anwar
Ahmed Anwar

Reputation: 125

Hey got the same question of Hacker Rank :-P

Here is the solution: 

select * from 
(select city, length(city) from station where length(city) = (select min(length(city)) from station) order by 1 limit 1) as tmp1
union all
select * from
(select city, length(city) from station where length(city) = (select max(length(city)) from station) order by 1 limit 1) as tmp2

we cannot use limit and order by clause with UNION clause. Thus we need to take alias of the result from both query and apply UNION clause.

Hope this will help :-)

Upvotes: 1

Paul Spiegel
Paul Spiegel

Reputation: 31832

City with the shortest name:

SELECT City, CHAR_LENGTH(City) AS len
FROM STATION
ORDER BY CHAR_LENGTH(City) ASC, City
LIMIT 1

City with the longest name:

SELECT City, CHAR_LENGTH(City) AS len
FROM STATION
ORDER BY CHAR_LENGTH(City) DESC, City
LIMIT 1

You can combine them into a single statement with UNION ALL:

SELECT *
FROM (
    SELECT City, CHAR_LENGTH(City) AS len
    FROM STATION
    ORDER BY CHAR_LENGTH(City) ASC, City
    LIMIT 1
) shortest 
UNION ALL (
    SELECT City, CHAR_LENGTH(City) AS len
    FROM STATION
    ORDER BY CHAR_LENGTH(City) DESC, City
    LIMIT 1
) longest

Upvotes: 3

tklodd
tklodd

Reputation: 1079

Here's my crack at it. This is untested, but it will give you something to start off of. (Edit: Now it is tested and it works. The union idea is probably better, but this would be the alternative using subqueries).

SELECT
    (SELECT City
    FROM table
    ORDER BY CHAR_LENGTH(City) DESC, City
    LIMIT 1) AS longest,
    (SELECT CHAR_LENGTH(City)
    FROM table
    ORDER BY CHAR_LENGTH(City) DESC, City
    LIMIT 1) AS longest_length,
    (SELECT City
    FROM table
    ORDER BY CHAR_LENGTH(City) ASC, City
    LIMIT 1) AS shortest,
    (SELECT CHAR_LENGTH(City)
    FROM table
    ORDER BY CHAR_LENGTH(City) ASC, City
    LIMIT 1) AS shortest_length
FROM table
LIMIT 1

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Here is a single query which you can try:

SELECT City, CHAR_LENGTH(City) AS length, 'max char length' AS description
FROM yourTable
WHERE CHAR_LENGTH(City) = (SELECT MIN(CHAR_LENGTH(City)) FROM yourTable)
ORDER BY City
LIMIT 1
UNION
SELECT City, CHAR_LENGTH(City) AS length, 'min char length' AS description
FROM yourTable
WHERE CHAR_LENGTH(City) = (SELECT MAX(CHAR_LENGTH(City)) FROM yourTable)
ORDER BY City
LIMIT 1

Upvotes: 1

Related Questions