Query the Two Cities in Station with the Shortest and Longest City Names,

Query to find the city name with longest and shortest length

Another way:

select * from (
         select top 1 city, LEN(city) cityLength from station order by cityLength ASC,city ASC) Minimum
       UNION
       select * from (
       select top 1 city, LEN(city) cityLength from station order by cityLength desc, city ASC) Maximum

Display the city with the shortest and longest name

Use ROW_NUMBER() twice to get the city with min and max length:

SELECT t.CITY, t.LEN 
FROM (
  SELECT CITY, LENGTH(CITY) LEN, 
    ROW_NUMBER() OVER (ORDER BY LENGTH(CITY), CITY) as rn1,
    ROW_NUMBER() OVER (ORDER BY LENGTH(CITY) DESC, CITY) as rn2
  FROM STATION 
) t
WHERE 1 IN (t.rn1, t.rn2)

See a simplified demo.

Select shortest and longest string

Anyway i got the answer

SELECT CITY,LENGTH(CITY)
FROM STATION
WHERE LENGTH(CITY) IN (
  SELECT MAX(LENGTH(CITY))
  FROM STATION
  UNION
  SELECT MIN(LENGTH(CITY))
  FROM STATION
)
ORDER BY LENGTH(CITY) DESC,CITY ASC LIMIT 2;

Hackerrank SQL problem to solve in Oracle's SQL version

A little bit of analytic functions; sample data in lines #1 - 6; query begins at line #7.

SQL> with station (city) as
  2    (select 'DEF'  from dual union all
  3     select 'ABC'  from dual union all
  4     select 'PQRS' from dual union all
  5     select 'WXY'  from dual
  6    )
  7  select city, len
  8  from (select city,
  9               length(city) len,
 10               rank() over (partition by length(city) order by city) rn
 11         from station
 12        )
 13  where rn = 1
 14  order by city;

CITY        LEN
---- ----------
ABC           3
PQRS          4

SQL>

---

Reading your comment, it seems you want something like this:

SQL> with station (city) as
  2   (select 'DEF'   from dual union all
  3    select 'ABC'   from dual union all
  4    select 'PQRS'  from dual union all
  5    select 'WXY'   from dual union all
  6    select 'XX'    from dual union all
  7    select 'ABCDE' from dual
  8   )
  9  select city, len
 10  from (select city,
 11               length(city) len,
 12               rank() over (order by length(city)     , city) rna,
 13               rank() over (order by length(city) desc, city) rnd
 14        from station
 15       )
 16  where rna = 1
 17     or rnd = 1
 18  order by len, city;

CITY         LEN
----- ----------
XX             2
ABCDE          5

SQL>

---

Related Topics