Query the two cities in STATION with the shortest and longest CITY names,

Question

Query: Query the 2 cities contained in STATION table 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.

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

this looks like an exercise question. show what you have tried to solve this. — Vamsi Prabhala, Aug 24 '16 at 17:29
yep I agree wiht vkp what have you tried? Hint you don't need nested or aggregate functions either so I untaged those. LENGTH() is the function that will give you the length of the city — Matt, Aug 24 '16 at 17:33
(select city, length(city) from station order by length(city) asc limit 1 ) union all (select city, length(city) from station order by length(city) desc limit 1 ); — krishna beenavoina, Aug 24 '16 at 17:51
above query worked for mysql but for oracle it is not working when i used top instead of limit — krishna beenavoina, Aug 24 '16 at 17:52
What Oracle version? (query `select * from v$version` to find out) Oracle 12 has something similar to `limit 1`, called `fetch first|last`; in Oracle 11 and below, it is a little more complicated. — , Aug 24 '16 at 17:54
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production — krishna beenavoina, Aug 24 '16 at 18:20
select s.city , length(s.city) from station as s join (select min(Length(s.city)) as minl , max(length(s.city)) as maxl from station s) ss on Length(s.city) in (ss.minl,ss.maxl) ORDER BY length(s.city) desc , s.city limit 2 — Manas Pratim Chamuah, Feb 28 '18 at 19:10
A simple solution for MYSQL server: 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, CITY ASC LIMIT 1; — fight_club, Oct 25 '20 at 03:47
one more solution with SQL server: 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)) — Seb, Jan 28 '22 at 16:11
select city,length(city) from station where id IN ((select id from station order by length(city) desc, city limit 1),(select id from station order by length(city) asc, city limit 1)); — amit prasad, Jul 27 '23 at 10:19

score 55 · Answer 1 · edited May 28 '20 at 07:49

55

TRY THIS :)

mysql code.... simple one

select CITY,LENGTH(CITY) from STATION order by Length(CITY) asc, CITY limit 1; 
select CITY,LENGTH(CITY) from STATION order by Length(CITY) desc, CITY limit 1;

Edit:

The above solution is not working for me as it doesn't sort alphabetically. As commented by @omotto the following is the proper way to make it work. I have tried in SQL server and it works.

select top 1 city, len(city) from station order by len(city) ASC, city ASC; 
select top 1 city, len(city) from station order by len(city) DESC, city ASC;

edited May 28 '20 at 07:49

Aamir Shahzad

6,683
8
47
70

answered Dec 22 '16 at 14:10

user7330784

561
4
3

@Moudiz It is, in fact, an answer, just didn't use code formatting - I edited to fix that. – EJoshuaS - Stand with Ukraine Dec 22 '16 at 15:58
1

So ordering by length, auto-alphabetically orders same length names? – Tolga Apr 06 '19 at 12:13
@Tolga did you find the answer? – Anil Aug 22 '19 at 06:34
2

This solution It's not ordering alphabetically: MS SQL solution: select top 1 city, len(city) from station order by len(city) ASC, city ASC; select top 1 city, len(city) from station order by len(city) DESC, city ASC; – omotto Feb 14 '20 at 12:16
@omotto your solution works as it sorts the items alphabetically ... not sure how all people have voted up that answer as it doesn't work – Aamir Shahzad May 28 '20 at 07:43
(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) -----this is what I did – Tanmoy Bhowmick Aug 22 '20 at 16:07
This works well in MS SQL Server but not Oracle. – Tim Nov 15 '20 at 19:30
I used the first code with the UNION clause and works fine to me in MySQL – Lucas Mar 12 '21 at 00:43

score 11 · Answer 2 · answered Dec 11 '16 at 19:07

11

For MS SQL Server:

Declare @Small int
Declare @Large int
select @Small = Min(Len(City)) from Station 
select @Large = Max(Len(City)) from Station
select Top 1 City as SmallestCityName,Len(City) as Minimumlength from Station where Len(City) = @Small Order by City Asc
select Top 1 City as LargestCityName,Len(City) as MaximumLength from Station where Len(City) = @Large Order by City Asc

For Oracle server:

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

answered Dec 11 '16 at 19:07

radhikesh93

870
9
25

Without using `distinct`, The solution for oracle works fine. – Mahedi Kamal Jul 07 '21 at 07:10
(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) – Rhushikesh Chaudhari Aug 20 '22 at 20:51

score 7 · Answer 3 · edited Apr 06 '17 at 12:44

7

( select CITY, 
       char_length(CITY) as len_city 
  from STATION 
  where char_length(CITY)=(select char_length(CITY) 
                          from STATION 
                          order by char_length(CITY) LIMIT 1) 
  Order by CITY LIMIT 1) 
 UNION ALL 
 (select CITY,
        char_length(CITY) as len_city 
  from STATION 
  where char_length(CITY)=(select char_length(CITY) 
                           from STATION 
                           order by char_length(CITY) DESC LIMIT 1)  
  Order by CITY DESC LIMIT 1) 
  ORDER BY char_length(CITY);

edited Apr 06 '17 at 12:44

Jaydip Jadhav

12,179
6
24
40

answered Jan 11 '17 at 15:17

Adithya_Kadri

79
1
3

3

Please provide some explanation as to why this answers the question – CallumDA Jan 11 '17 at 15:36
2

Welcome to Stack Overflow! While this code snippet may solve the question, [including an explanation](//meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. Please also try not to crowd your code with explanatory comments, this reduces the readability of both the code and the explanations! – Martin Tournoij Jan 11 '17 at 19:06
@Carpetsmoker will add the explanations anymore whenever i post an answer. – Adithya_Kadri Jan 18 '17 at 13:34

score 6 · Answer 4 · edited Oct 27 '17 at 07:04

6

select min(city), len
  from (
        select city, length(city) len,
               max(length(city)) over() maxlen,
               min(length(city)) over() minlen
          from station
       )
 where len in(minlen,maxlen)
 group by len

Subquery gets the list of cities and it's length. At the same time "window functions" min/max over() get minimal and maximal length for all rows in set (table). Main query filter only cities of length is min/max. min(city) with the group by len gives the result first name on the alphabetical order.

edited Oct 27 '17 at 07:04

Community

1
1

answered Aug 24 '16 at 19:18

Mike

1,985
1
8
14

Please provide an explanation with your code, so the OP can learn from it. – EBH Aug 24 '16 at 20:39
1

@EBH Ok. updated. I not speak english. Please correct my answer if it contains grammatical errors. – Mike Aug 24 '16 at 21:19

score 4 · Answer 5 · answered Aug 24 '16 at 18:58

4

Here is another way to do it using the always handy row_number analytic function:

with cte as (
  select city,
         length(city) as len,
         row_number() over (order by length(city), city) as smallest_rn,
         row_number() over (order by length(city) desc, city) as largest_rn
    from station
)
select city, len
  from cte
 where smallest_rn = 1
union all
select city, len
  from cte
 where largest_rn = 1

answered Aug 24 '16 at 18:58

sstan

35,425
6
48
66

Correct answer for your hackerrank test case is : 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; – Tamara Koliada Oct 29 '22 at 11:14
Another way is to use row number function and derived table... SELECT city, lencity FROM ( select city, len(city) lencity, row_number() over (order by len(city) ASC, city) shortestCitySort, row_number() over (order by len(city) DESC, city) largestCitySort from station )A where A.shortestCitySort = 1 OR A.largestCitySort = 1 order by lencity – Shaggy Apr 11 '23 at 23:15
Tested and passed successfully SELECT CITY, LENGTH(CITY) AS noofchar FROM STATION ORDER BY noofchar, CITY ASC LIMIT 1; SELECT CITY, LENGTH(CITY) AS noofchar FROM STATION ORDER BY noofchar DESC, CITY ASC LIMIT 1; – Juliane Foo Jun 05 '23 at 11:32

score 4 · Answer 6 · edited Nov 12 '20 at 04:49

4

  SELECT * FROM (SELECT city, length(city) 
  FROM station
  WHERE LENGTH(city)=(SELECT MIN(LENGTH(city)) FROM station) ORDER BY city ) 
  WHERE ROWNUM =1;

  SELECT city, LENGTH(city) 
  FROM station  
  WHERE LENGTH(city)=(SELECT MIN(LENGTH(city)) FROM STATION) 
  AND ROWNUM=1
  ORDER BY CITY;

edited Nov 12 '20 at 04:49

sonique

4,539
2
30
39

answered Feb 19 '17 at 16:51

Ashish Sharma

41
4

3

Welcome to Stack Overflow! Take a minute to read through [How to Answer](http://stackoverflow.com/questions/how-to-answer) - this looks helpful but it would benefit from some explanation of what the code does, consider [edit](http://stackoverflow.com/posts/41385887/edit)-ing that in? – Timothy Truckle Feb 19 '17 at 16:59
SELECT CITY, L FROM ( SELECT CITY, LENGTH(CITY) AS L, DENSE_RANK() OVER(ORDER BY LENGTH(CITY),CITY) city_length_mn, DENSE_RANK() OVER(ORDER BY LENGTH(CITY) DESC,CITY) city_length_mx FROM STATION ) A WHERE A.city_length_mn = 1 OR A.city_length_mx = 1 ORDER BY CITY; – rcr Aug 11 '19 at 19:28

Query the two cities in STATION with the shortest and longest CITY names,

6 Answers6

Linked