shortest city query

query station

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.

select CITY, LEN(CITY) from STATION;

says wrong answer
please advise
LVL 7
gudii9Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
You asked this in both SQL Server and Oracle.

Which database do you want this for?

A lot of your other questions are Oracle so here is one way to get them both as a single row.  It should also work in SQL Server if you change the LENGTH function.

select
	max(case when min_rn=1 then city end) shortest_city,
	max(case when min_rn=1 then city_len end) shortest_length,
	max(case when max_rn=1 then city end) longest_city,
	max(case when max_rn=1 then city_len end) longest_length
from(
	select city,length(city) city_len,
		row_number() over(order by length(city)) min_rn,
		row_number() over(order by length(city) desc) max_rn
	from station
)
/

Open in new window

1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you answering to an exam?
You can do this in many ways. Here's one:
SELECT TOP 1 'Shortest', City, LEN(City) LengthCity
FROM station
ORDER BY LEN(city), City
UNION ALL
SELECT TOP 1 'Longest', City, LEN(City) LengthCity
FROM station
ORDER BY LEN(city) DESC, City

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
and for the SQL server equivalent of slightwv's code - very similar
select distinct city, len_city
from
( select city, len(city) as len_city
       , row_number() over (order by len(city),city) as short_city
       , row_number() over (order by len(city) desc,city asc) as long_city
  from station ) s
where 1 in (short_city,long_city)
order by city

Open in new window


and slightwv is quite correct, if you change length(city) to len(city) then his version will also work for SQL Server.
0
 
Pavel GushchinProgrammerCommented:
select distinct 
       first_value(city) over(order by nvl(length(city),0), city) shortest,
       first_value(city) over(order by nvl(length(city),0) desc, city) longest
from station

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Pavel, what makes you think that only your comment is correct?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.