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)

`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;` But the error said there is a syntax problem with union. I excluded the union and it worked fine. Can you correct me on using union here? @Anatolii — Proteeti Prova, Sep 25 '18 at 07:31

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

2

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,186
3
20
39

answered Jan 18 '20 at 16:27

sayantan Das

21
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 · edited Sep 15 '18 at 23:03

0

Use the following query:

(Select * from (Select city, length(city) from station order by length(city), city) where rownum = 1) Union All
(Select * from (Select city, length(city) from station order by length(city) desc, city) where rownum = 1);

edited Sep 15 '18 at 23:03

Elletlar

2,991
7
29
34

answered Sep 15 '18 at 21:31

Shruti Srivastava

1

3

Welcome to Stack Overflow. While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.[How to Answer](https://stackoverflow.com/help/how-to-answer) – Elletlar Sep 15 '18 at 21:37

score 0 · Answer 5 · 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

score 0 · Answer 6 · 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 7 · 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 8 · 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;

score 0 · Answer 9 · edited May 03 '20 at 07:11

0

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

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

edited May 03 '20 at 07:11

tomerpacific

3,871
11
28
48

answered May 03 '20 at 03:39

Rivu Dass

1

Anusuya Roy · Answer 10 · 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

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 11 · 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 12 · answered Nov 13 '20 at 10:57

0

select city,lens from (select city,Length(city) 'lens' from station order by lens asc,city asc )as zz limit 1;
select city,lens from (select city,Length(city) 'lens' from station order by lens desc ,city asc )as zz limit 1;

This worked for me hope it helps.

answered Nov 13 '20 at 10:57

devansh

91
1
6

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value. [How to Answer](https://stackoverflow.com/help/how-to-answer) – Elletlar Nov 13 '20 at 12:05

score 0 · Answer 13 · 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 14 · 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

43,040
110
45
72

answered Jun 01 '21 at 14:22

Twinkle Paul

1
1

score 0 · Answer 15 · 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

3,262
10
15
39

answered Jul 06 '21 at 11:54

Malaya Ranjan Aich

1

score 0 · Answer 16 · answered Sep 18 '21 at 02:25

0

for oracle

select city, length(city) from(select city, length(city) from station group by city order by length(city),city) where rownum=1;

select city, length(city) from(select city, length(city) from station group by city order by length(city) DESC,city) where rownum=1;

answered Sep 18 '21 at 02:25

Mounesh Pattar

1
1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-ask). – Community Sep 18 '21 at 05:50

score 0 · Answer 17 · answered Jan 20 '22 at 11:38

0

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

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

answered Jan 20 '22 at 11:38

ahmed assad

11
1

1

The same answer has already been posted. – Syscall Jan 20 '22 at 12:02
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 20 '22 at 12:58

Seb · Answer 18 · 2022-01-28T17:20:04.507

one more solution with 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))

score 0 · Answer 19 · 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.

HackerRank Weather Observation Station 5

19 Answers19