HackerRank Weather Observation Station 5

Question

I would like to know why my code isnt working. This question has been asked before here: Query the two cities in STATION with the shortest and longest CITY names,

and solution here: https://github.com/chhayac/SQL-hackerrank-problems/blob/master/basic-select.md

But both answers do not work. I have pasted the question below followed by my solution. Thanks for the help!

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.

Input Format

The STATION table is described as follows:

Station.jpg

where LAT_N is the northern latitude and LONG_W is the western longitude.

Sample Input

Let's say that CITY only has four entries: DEF, ABC, PQRS and WXY

Sample Output

ABC 3
PQRS 4

Explanation

When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with the respective lengths and . The longest-named city is obviously PQRS, but there are options for shortest-named city; we choose ABC, because it comes first alphabetically.

Note You can write two separate queries to get the desired output. It need not be a single query.

MY ANSWER:

/shortest character length sorted in alphabetical order/

SELECT city, LENGTH(city) as length_char
FROM station
ORDER BY LENGTH(city) ASC, city ASC
LIMIT 1;

/longest character length sorted in alphabetical order/

SELECT city, LENGTH(city) as length_char
FROM station
ORDER BY LENGTH(city) DESC
LIMIT 1;

I would like to know why my answer is wrong and secondly the solution to the problem. — Jassim, Jun 08 '18 at 12:50

Anatolii · Answer 1 · 2018-09-25T08:29:11.237

Your solution on github looks as follows:

select city, length(city) from station order by length(city) DESC,city ASC fetch first row only;
select city, length(city) from station order by length(city) asc ,city asc fetch first row only;

You have a problem here - there's no such command as fetch first row only. Depending on the database system it can be top, limit or rownum - please read more here - https://www.w3schools.com/sql/sql_top.asp

So, depending on the system the answer will be different as well.

Oracle

select * from (select city c, length(city) l
from   station
order by l desc, c asc)
where rownum = 1;

select * from (select city c, length(city) l
from   station
order by l asc, c asc)
where rownum = 1;

SQL Server

select top 1 city c, len(city) l
from   station
order by l desc, c asc;

select top 1 city c, len(city) l
from   station
order by l asc, c asc;

MySQL

select city c, length(city) l
from   station
order by l desc, c asc
limit 1;

select city c, length(city) l
from   station
order by l asc, c asc
limit 1;

or with the use of union:

(select city, length(city) 
from station 
order by length(city) asc , city asc limit 1) 
union
(select city,length(city) 
from station 
order by length(city) desc, city asc limit 1)

it doesn't work. It gives error : DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0084N An EXECUTE IMMEDIATE statement contains a SELECT or VALUES statement. SQLSTATE=42612 — Karen Goh, Feb 08 '23 at 09:37

score 4 · Answer 2 · edited Jan 18 '20 at 18:39

4

SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY ASC LIMIT 1;

SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC,CITY ASC LIMIT 1; 

/* FOR MYSQL */

edited Jan 18 '20 at 18:39

Mickael B.

4,755
4
24
48

answered Jan 18 '20 at 16:27

sayantan Das

41
1

score 1 · Answer 3 · answered May 13 '19 at 11:48

select t2.city , t2.t  
from  
(  
    select t1.city , t1.t , row_number() over (partition by t1.t order by t1.city) as ro 
    from
        ( select city , length(city)as t 
          from station 
        ) t1
    group by t1.city,t1.t
    having 
        t1.t = (select min(length(city)) from station )
                           or 
        t1.t = (select max(length(city)) from station)
) t2
where t2.ro = 1    ;

table t2 will give all the records which have min and max string length along with a row numbering now filtering records based on row num will fetch you desired output

score 0 · Answer 4 · answered Sep 20 '18 at 22:21

0

My hit on this question was :-

SELECT CITY,LENGTH(CITY)

FROM STATION

ORDER BY LENGTH(CITY),CITY

LIMIT 1 OFFSET 0;



SELECT CITY,LENGTH(CITY)

FROM STATION

ORDER BY LENGTH(CITY) DESC,CITY

LIMIT 1 OFFSET 0;

answered Sep 20 '18 at 22:21

Vikrant Sharma

1
2

Please read [answer] and [edit] your answer to contain an explanation as to why this code would actually solve the problem at hand. Always remember that you're not only solving the problem, but are also educating the OP and any future readers of this post. – Adriaan Oct 31 '22 at 09:23

score 0 · Answer 5 · answered Mar 25 '19 at 00:21

Actually your codes seems right. I think the only problem could be the setting work place as not "MYSQL". If you are running your codes on "MS SQL Server" it will give you some "built-in function" problems (Like on mysql its written lengt() but on ms sql server its written len()) (Or "Limit 1" etc.)

One of the other solution which I tried was (on MS SQL Server);

For finding the longest character city (alphabetically first) ;

Select TOP 1 city, LEN(CITY)
From station 
Where  
len(city) = (select max(len(city)) from station ) 
Order By city asc ;

For finding the shortest character city (alphabetically first) ;

Select TOP 1 city, LEN(CITY)
From station
Where
len(city) = (select min(len(city)) from station) 
Order By city asc ;

score 0 · Answer 6 · answered May 07 '19 at 17:51

0

SELECT city, CHAR_LENGTH(city) 
FROM station
ORDER BY CHAR_LENGTH(city), city
LIMIT 1;

SELECT city, CHAR_LENGTH(city) 
FROM station
ORDER BY CHAR_LENGTH(city) desc, city desc
LIMIT 1;

answered May 07 '19 at 17:51

realleyriley

21
5

While the shared code may answer the question, adding a bit of context or explanation is generaly useful and improve the quality of the answer. – Valentino May 07 '19 at 18:13

score 0 · Answer 7 · answered Feb 19 '20 at 17:23

You can also use this query as a slightly different answer. I used a subquery in the WHERE clause in MySQL

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

Anusuya Roy · Answer 8 · 2020-07-16T11:04:44.830

0

With MySQL solution:

SELECT CITY, length(CITY) FROM STATION ORDER BY length( CITY), CITY ASC limit 1;

SELECT CITY, length(CITY) FROM STATION ORDER BY length(CITY) DESC limit 1;

edited Jul 16 '20 at 11:04

answered Jul 16 '20 at 10:31

Anusuya Roy

29
4

1

While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Tschallacka Jul 16 '20 at 11:03

score 0 · Answer 9 · edited Jul 30 '20 at 09:18

Follow for MYSQL-

(SELECT  CITY , LENGTH(CITY) AS CITY_LENGTH
    FROM STATION
    ORDER BY CITY_LENGTH DESC, CITY ASC
    LIMIT 1)
UNION ALL
(SELECT  CITY , LENGTH(CITY) AS CITY_LENGTH
    FROM STATION
    ORDER BY CITY_LENGTH ASC, CITY ASC
    LIMIT 1)

follow comments for better explanation- AS keyword- for Alias ASC/DESC - Ascending/Descending order LIMIT function - To limit the query output Union function - To aggregate the results

score 0 · Answer 10 · answered Apr 30 '21 at 07:42

For Oracle ==> I solved it using sub-query and used "MIN" and "MAX" funtions inside subquery so I skipped using "ORDER BY" as "MIN","MAX" functions are doing the same thing here, just used "ORDER BY" for outer query to order the final output by length.(hope it helps!!)

SELECT CITY, LENGTH(CITY)
FROM STATION
WHERE CITY=(SELECT MIN(CITY)
            FROM STATION
            WHERE LENGTH(CITY)=(SELECT MIN(LENGTH(CITY))
                                FROM STATION)) OR
      CITY=(SELECT MIN(CITY)
            FROM STATION
            WHERE LENGTH(CITY)=(SELECT MAX(LENGTH(CITY))
                                FROM STATION))
ORDER BY LENGTH(CITY);

score 0 · Answer 11 · edited Jun 01 '21 at 14:50

0

For Oracle - using Dense Rank:

SELECT CITY, LENGTH(CITY) FROM (
    SELECT CITY, LENGTH(CITY), 
        DENSE_RANK() OVER (ORDER BY LENGTH(CITY) ASC, CITY ASC) ROW1
    FROM STATION
)WHERE ROW1 = 1 
UNION
SELECT CITY, LENGTH(CITY) FROM (
    SELECT CITY, LENGTH(CITY), 
        DENSE_RANK() OVER (ORDER BY LENGTH(CITY) DESC, CITY ASC) ROW2
    FROM STATION
)WHERE ROW2 = 1;

edited Jun 01 '21 at 14:50

Adrian Mole

49,934
160
51
83

answered Jun 01 '21 at 14:22

Twinkle Paul

1
1

score 0 · Answer 12 · edited Jul 07 '21 at 21:14

0

(select city,length(city) from station 
order by  length(city) desc,city asc limit 1)
union
(select city,length(city) from station 
order by length(city) asc,city asc limit 1);

This is working in MySQL.

edited Jul 07 '21 at 21:14

buddemat

4,552
14
29
49

answered Jul 06 '21 at 11:54

Malaya Ranjan Aich

1

Please read [answer] and [edit] your answer to contain an explanation as to why this code would actually solve the problem at hand. Always remember that you're not only solving the problem, but are also educating the OP and any future readers of this post. – Adriaan Oct 31 '22 at 09:24

score 0 · Answer 13 · edited Oct 31 '22 at 09:24

0

You can do this in SQL server with a single query:

SELECT TOP 1* 
FROM (SELECT DISTINCT TOP 1 CITY, LEN(CITY) C1 
      FROM STATION 
      ORDER BY LEN(CITY) ASC, CITY ASC) ONE
GROUP BY CITY, LEN(CITY), C1 
HAVING LEN(CITY) = MIN(LEN(CITY)) 

UNION 

SELECT TOP 1* 
FROM (SELECT DISTINCT TOP 1 CITY, LEN(CITY) C2 
      FROM STATION
      ORDER BY LEN(CITY) DESC, CITY DESC) TWO 
GROUP BY CITY, LEN(CITY), C2 
HAVING LEN(CITY) = MAX(LEN(CITY))

edited Oct 31 '22 at 09:24

Adriaan

17,741
7
42
75

answered Jan 28 '22 at 16:18

Seb

85
9

Please read [answer] and [edit] your answer to contain an explanation as to why this code would actually solve the problem at hand. Always remember that you're not only solving the problem, but are also educating the OP and any future readers of this post. – Adriaan Oct 31 '22 at 09:24

score 0 · Answer 14 · answered Apr 09 '22 at 19:22

here is my solution to this particular problem

select city,CHAR_LENGTH(city) from station
order by CHAR_LENGTH(city), city
limit 1;

select city,CHAR_LENGTH(city) from station
order by CHAR_LENGTH(city) DESC,city
limit 1;

this approach is pretty intuitive and easy to understand.

Nabeel Seid · Answer 15 · 2022-06-05T17:37:46.050

0

Everything under a single SELECT statement.

SELECT CITY, LENGTH(CITY)
FROM station
WHERE CITY = (SELECT CITY
              FROM station
              ORDER BY LENGTH(CITY), CITY
              LIMIT 1)
   OR CITY = (SELECT CITY
              FROM station
              ORDER BY LENGTH(CITY) DESC, CITY
              LIMIT 1);

A better and cleaner solution.

SELECT CITY, LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY), CITY
LIMIT 1
UNION ALL -- UNION removes duplicate records, UNION ALL does not.
SELECT CITY, LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY) DESC, CITY
LIMIT 1;

edited Jun 05 '22 at 17:37

answered Jun 05 '22 at 17:35

Nabeel Seid

7
3

What is this answer adding to the other 19 answers already provided? – Shmiel Jun 07 '22 at 16:47

score 0 · Answer 16 · edited Sep 20 '22 at 09:56

0

For MS SQL easy solution:

SELECT TOP 1 city,
             Min(Len(city))
FROM   station
GROUP  BY city,
          Len(city)
ORDER  BY Len(city) ASC;

SELECT TOP 1 city,
             Min(Len(city))
FROM   station
GROUP  BY city,
          Len(city)
ORDER  BY Len(city) DESC;

edited Sep 20 '22 at 09:56

Siddhartha Mukherjee

2,703
2
24
29

answered Sep 13 '22 at 17:37

Shafiqur Rohman

1
1

Please read [answer] and [edit] your answer to contain an explanation as to why this code would actually solve the problem at hand. Always remember that you're not only solving the problem, but are also educating the OP and any future readers of this post. – Adriaan Oct 31 '22 at 09:23

HackerRank Weather Observation Station 5

16 Answers16