Q: SQL query for finding the longest name and shortest name in a table I have a table with one of the columns is of type varchar(city). and want to find the longest and shortest of values stored in that column.
select a.city, a.city_length from (select city, char_length(city) city_length 
from station order by city, city_length) a
where a.city_length = (select min(a.city_length) from a) or
      a.city_length = (select max(a.city_length) from a)
group by a.city_length;

Can anyone help? Thanks

One solution:
select * from (select city, char_length(city) city_length from station order by city, city_length) a group by a.city_length order by a.city_length limit 1;
select * from (select city, char_length(city) city_length from station order by city, city_length) a group by a.city_length order by a.city_length desc limit 1;


A: Maybe a simpler option since I imagine you are looking for help with a solution to a Hacker Rank question? The addition of limits made it simpler for me to debug where the issue was with the returned error.
SELECT city, length(city) FROM station order by length(city) desc limit 1;

SELECT city, length(city) FROM station order by length(city) asc, city asc limit 1


A: You query requires just a few tweaks.  The fundamental problem is that you cannot use a in the subquery as you are doing:
select a.city, a.city_length
from (select city, char_length(city) city_length 
      from station 
     ) a
where a.city_length = (select min(char_length(city)) from station) or
      a.city_length = (select max(char_length(city)) from station);

That said, a simpler way to write the query is:
select s.*
from station s cross join
     (select min(char_length(city)) as mincl, max(char_length(city)) as maxcl
      from station
     ) ss
where char_length(s.city) in (mincl, maxcl);


A: I don't think that we need to use Min and Max functions and Group by is also not required. 
We can achieve this using the below code:
select top 1 City, LEN(City) City_Length from STATION order by City_Length ASC,City ASC

select top 1 CITY, LEN(city) City_Length from station order by City_Length desc, City ASC

but in this case, it will display output in 2 table and if we would like to combine in a single table then we can use Union or Union ALL. Below is the SQL query for the same
  select * from (
     select top 1 City, LEN(City) City_Length from STATION order by City_Length ASC,City ASC) TblMin
   UNION
   select * from (
   select top 1 CITY, LEN(city) City_Length from STATION order by City_Length desc, City ASC) TblMax

here I am nesting the select statement inside a subquery because when we are using order by clause then we cannot use Union or Union ALL directly that is why I have written it inside a subquery.

A: In Oracle:
select * from (select city, min(length(city)) minl from station group by city order by minl, city) where rownum = 1;
select * from (select city, max(length(city)) maxl from station group by city order by maxl desc, city) where rownum = 1;

A: Ascending:
SELECT city, CHAR_LENGTH(city) FROM station ORDER BY CHAR_LENGTH(city), city LIMIT 1;
Descending:
SELECT city, CHAR_LENGTH(city) FROM station ORDER BY CHAR_LENGTH(city) DESC, city LIMIT 1;

A: This is another way of doing it in MySQL. May not be the best, but still be an option that is logically correct.
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;


A: Shortest:
select TOP 1 CITY,LEN(CITY) LengthOfCity FROM STATION ORDER BY LengthOfCity ASC, CITY ASC;

Longest:
select TOP 1 CITY,LEN(CITY) LengthOfCity FROM STATION ORDER BY LengthOfCity DESC, CITY ASC;

This works for HackerRank challenge problem (MS SQL Server).

A: In MySQL
(select city, LENGTH(city) cityLength  from station order by cityLength desc,city asc LIMIT 1)
    union all
    (select city, LENGTH(city) cityLength  from station order by cityLength asc,city asc LIMIT 1)


A: This is an approach with a CTE. First it finds the longest and shortest, than the matching cities:
DECLARE @tbl TABLE(CityName VARCHAR(100));
INSERT INTO @tbl VALUES ('xy'),('Long name'),('very long name'),('middle'),('extremely long name');

WITH MyCTE AS 
(
    SELECT MAX(LEN(CityName)) AS Longest
          ,MIN(LEN(CityName)) AS Shortest
    FROM @tbl
)
SELECT * 
FROM MyCTE
--You must think about the chance of more than one city matching the given length
CROSS APPLY(SELECT TOP 1 CityName FROM @tbl WHERE LEN(CityName)=Longest) AS LongestCity(LongName)
CROSS APPLY(SELECT TOP 1 CityName FROM @tbl WHERE LEN(CityName)=Shortest) AS ShortestCity(ShortName)

The result
Longest Shortest    LongName               ShortName
19       2          extremely long name    xy


A: I did this in SQL Server using CTE and dense_rank function. 
How the ranking works?
First partition (form groups) over the lengths, i.e same lengths make a group (partition). Then order all the names alphabetically within each partition. Then assign ranks (dRank column) within each partition. So rank 1s in each group will be assigned to names which alphabetically appear first in their respective partition. All this happens in the common table expression (cte block)
"with cte as
(
select *, LEN(city) as length, DENSE_RANK() over (partition by len(city) order by city) as dRank from Station
)"

select city,length from cte where dRank = 1 and length = (select MIN(length) from cte)
UNION
select city,length from cte where dRank = 1 and length = (select max(length) from cte)"


A: select top(1) city, max(len(city)) [Length] from station group by city order by [Length]
select top(1) city, max(len(city)) [Length] from station group by city order by [Length] DESC

Tested in SQL Server 2016

A: For shortest name of city : 
SELECT ST.CITY,LENGTH(ST.CITY) AS LENGTH FROM STATION ST
ORDER BY LENGTH ASC, ST.CITY ASC
LIMIT 1;

For longest name of city : 
SELECT ST.CITY,LENGTH(ST.CITY) AS LENGTH FROM STATION ST
ORDER BY LENGTH DESC, ST.CITY DESC
LIMIT 1;


A: I used the WITH clause for Oracle to save the shortest /longest names in a temporary variable rather than querying inside the main "from" clause.
SQL WITH clause example
WITH shortnames AS
(SELECT city, length(city) 
 FROM station 
 ORDER BY length(city) asc, city),

 longnames AS
 (SELECT city, length(city) 
  FROM station
  ORDER BY length(city) desc, city)

SELECT * FROM shortnames WHERE ROWNUM=1
UNION ALL
SELECT * FROM longnames WHERE ROWNUM=1;


A: Initially finding the shortest length of the city and taking an union with the longest length of the city. This minimizes the complexity of the query.
(select city, char_length(city) as len_city
from station
order by len_city limit 1)
union ( select city, char_length(city) as len_city
    from station
    order by len_city desc limit 1) 
order by len_city


A: In Oracle (and any other language that supports analytic functions), using the ROW_NUMBER analytic function you can assign the rows a unique number according to ASCending (or DESCending) length of the city. Since there may be multiple rows with the same length then a secondary order can be applied to get the first city of that length alphabetically. Then all you need is an outer query to filter the results to only the shortest (or longest) name:
SELECT city
FROM   (
  SELECT CITY,
         ROW_NUMBER() OVER ( ORDER BY LENGTH( CITY ) ASC,  CITY ) shortest_rn,
         ROW_NUMBER() OVER ( ORDER BY LENGTH( CITY ) DESC, CITY ) longest_rn
  FROM   station
)
WHERE shortest_rn = 1
OR    longest_rn  = 1;

If you want to return all the cities with the shortest (or longest) name then use DENSE_RANK instead of ROW_NUMBER:
SELECT city
FROM   (
  SELECT CITY,
         DENSE_RANK() OVER ( ORDER BY LENGTH( CITY ) ASC  ) shortest_rn,
         DENSE_RANK() OVER ( ORDER BY LENGTH( CITY ) DESC ) longest_rn
  FROM   station
)
WHERE shortest_rn = 1
OR    longest_rn  = 1
ORDER BY shortest_rn, city; -- Shortest first and order tied lengths alphabetically


A: In Oracle 12c, this could be done using FETCH..FIRST
Shortest
select * FROM station ORDER BY LENGTH(city) DESC FETCH FIRST 1 ROWS ONLY;

Longest
select * FROM station ORDER BY LENGTH(city) ASC FETCH FIRST 1 ROWS ONLY;


A: For oracle :
select min(city),length(city) from station where length(city) <= all(select 
length(city) from station) group by length(city);

select max(city),length(city) from station where length(city) >= all(select 
length(city) from station) group by length(city);


A: Following query seems simple enough: 
select 
    city, leng 
from
    (select top 1 
         city, len(city) leng 
     from 
         station 
     where 
         len(city) = (select min(len(city)) from station) 
     order by 
         city

     Union all

     select top 1 
         city, len(city) leng 
     from 
         station 
     where 
         len(city) = (select max(len(city)) from station)  
     order by 
         city) result;

The 1st query inside returns the city with the minimum length, while the 2nd query returns the city with the maximum length. 
Hope this helps.

A: In Oracle it would be, 
SELECT CITY,LENGTH(CITY) FROM   (SELECT MIN(CITY) CITY  FROM STATION WHERE LENGTH(CITY)=(SELECT MIN(LENGTH(CITY)) FROM STATION))
UNION ALL
SELECT CITY,LENGTH(CITY) FROM   (SELECT MAX(CITY) CITY  FROM STATION WHERE LENGTH(CITY)=(SELECT MAX(LENGTH(CITY)) FROM STATION));    


A: This Query will work well in your condition these 2 query are exactly same. In the first query we just ordering the recording in descending order to take the city name with the highest length and in the second part we are just taking records in the ascesding order to take city with the minimum lenght all the rest query is same. Kidnly have a look.
Detail:


*

*Select statement to take out records 

*Use group by clause because I am using Len() aggregating function.

*Ordering all the retrieved records in the descending and ascending order to get the top 1 and take max and min length city.
select  Top 1  City,max(len(City)) as Length  
from STATION 
group by City 
ORDER BY Length desc 

select  Top 1  City,max(len(City)) as Length  
from STATION 
group by City 
ORDER BY Length ASC


A: For oracle SQL in one resulting table. This will retrieve the min and max city names, and if same length will get the first city sorted in alphabetic order.
SELECT * FROM (
    SELECT CITY, LENGTH(CITY) CITY_LENGTH 
    FROM STATION 
    ORDER BY CITY_LENGTH ASC, CITY ASC ) MAX_LEN  
WHERE ROWNUM <= 1
UNION
SELECT * FROM (
    SELECT CITY, LENGTH(CITY) CITY_LENGTH 
    FROM STATION 
    ORDER BY  CITY_LENGTH DESC, CITY ASC ) MIN_LENGTH  
WHERE ROWNUM <= 1;


A: select city,length(city) from (select city,length(city),rank() over(partition by length(city) order by length(city),city asc) as rnk from station)a where a.rnk=1;

A: In Oracle, we can do like this
select city, length(city)
from (select city from STATION order by length(city) DESC, city ASC)
where rownum = 1 
union
select city, length(city)  
from (select city from STATION order by length(city), city ASC)
where rownum = 1;

The idea is to get the longest and shortest city then union them into one result.

A: SELECT MAX(LENGTH(transaction_id)) AS Longest ,MIN(LENGTH(transaction_id)) AS Shortest FROM cards


A: length(CITY) will return the length of the string,
https://www.w3schools.com/sql/func_mysql_length.asp
(select CITY, length(CITY) from STATION order by length(CITY),CITY limit 1)
UNION
(select CITY, length(CITY) from STATION order by length(CITY) DESC limit 1);


A: For Oracle this worked for me(HackerRank)
--shortest 
SELECT CITY,L FROM 
(select CITY,length(CITY) L
from STATION   
order by length(CITY) ASC, CITY ASC)
WHERE ROWNUM=1
UNION ALL 
--longest 
SELECT CITY,L FROM 
(select CITY,length(CITY) L
from STATION   
order by length(CITY) DESC, CITY ASC)
WHERE ROWNUM=1;

Texto original
I tried many ways, but this work, I ordered by length, and after alphabetically, and finally get first row only.

A: This works for mysql
-- smallest
SELECT city, length(city) FROM station 
WHERE length(city) = (select min(length(city)) from station)
ORDER BY city
limit 1;

-- largest
SELECT city, length(city) FROM station 
WHERE length(city) = (select max(length(city)) from station)
ORDER BY city
limit 1;


A: Here is a solution that strictly uses MIN and MAX
The concept here is to apply MIN to the length of the city name. Within those results, again apply MIN to city name itself to find the first alphabetically.
Of course, use MAX in the same way to find the maximum city length, and perform a UNION of the two queries for a final solution.
No sorts, ASC, DESC, LIMIT, or ORDER BY needed.
Great reference:
https://www.zentut.com/sql-tutorial/sql-min-max/
(
    SELECT s3.CITY, s3.Len 
    FROM (
        SELECT CITY, s1.Len
        FROM (
            SELECT CITY, LENGTH(CITY) AS Len
            FROM STATION
        ) s1
        WHERE
            s1.Len = (
                SELECT MIN(s2.Len2)
                FROM (
                    SELECT LENGTH(CITY) AS Len2
                    FROM STATION
                ) s2
            )
        ) s3
    WHERE
        s3.CITY = (
            SELECT MIN(CITY)
            FROM STATION
            WHERE s3.Len = LENGTH(CITY)
        )
) UNION (
    SELECT s3.CITY, s3.Len 
    FROM (
        SELECT CITY, s1.Len
        FROM (
            SELECT CITY, LENGTH(CITY) AS Len
            FROM STATION
        ) s1
        WHERE
            s1.Len = (
                SELECT MAX(s2.Len2)
                FROM (
                    SELECT LENGTH(CITY) AS Len2
                    FROM STATION
                ) s2
            )
        ) s3
    WHERE
        s3.CITY = (
            SELECT MIN(CITY)
            FROM STATION
            WHERE s3.Len = LENGTH(CITY)
        )
);


A: In addition to all answers above, I want to share a trick I used in T-SQL but exist in any version:

*

*I calculate the length with leading 0 pads which allow alphabetic sort

*

*In this example, I assume all text will be less than 10MB, so 7 digit number at max

*REPLACE(STR(CAST(LEN(city) AS INT) ,7), SPACE(1), '0')



*I append the 0-padded LEN() to the value

*

*concat( <step1> , city )



*I choose MAX() or MIN()

*

*This works because the 0 padded number can be sorted alphabetically.

*Example: "099" < "100", but "99" > "100" because "9" > "1" in ascii)



*I remove the number from the value

*

*STUFF( <step 3> ,1,7,'') as we replace 7 firts chars with '', and our number will always be 7 char long because of 0 padding



Finally, the following SQL will return both answers in the same query, without windows and without unions:
SELECT
    STUFF(min(
        concat(
            REPLACE(STR(CAST(LEN(city) AS INT) ,7), SPACE(1), '0')
            ,city)) 
        ,1,7,'')
        as [min_len],
    STUFF(max(
        concat(
            REPLACE(STR(CAST(LEN(city) AS INT) ,7), SPACE(1), '0')
            ,city)) 
        ,1,7,'')
        as [max_len]
from station

This also lets you get min max by any other info from any other column (in the same row), like "station_ranking" column if exists.
Here is a dbfiddle with MySQL example:
SELECT
    SUBSTR(min(
        concat(
            LPAD(length(city), 7, '0') 
            ,city)) 
        ,7+1)
        as min_name_len,
    SUBSTR(max(
        concat(
            LPAD(station_rank, 2, '0') 
            ,city)) 
        ,2+1)
        as max_name_rank
from stations

https://www.db-fiddle.com/f/76ew1fK9D5V3vopbGUuYxh/4

A: with cte (rank, city , CityLength) 
As 
(select  dense_rank() over (partition by len(city) order by city asc) as Rank, city, len(city) 
    from station 
where len(city) in 
    ((select max(len(city)) from station) 
    union (select min(len(city)) from station)))
select city,citylength from cte where rank = 1;


A: select * from (select city,length(city)from station group by city having length(city) in ((select min(length(city))from station))order by city) where rownum<2
UNION
select * from (select city,length(city)from station group by city having length(city) in ((select max(length(city))from station))order by city) where rownum<2;


A: I think this should work:
    SELECT MAX(CITY) , LENGTH(MAX(CITY)) FROM STATION;
    SELECT MIN(CITY) , LENGTH(MIN(CITY)) FROM STATION;


A: Shortest:
select city, char_length(city) city_length from station order by city_length, city limit 1;

Longest:
select city, char_length(city) city_length from station order by city_length desc, city limit 1;


A: For Oracle Database:
select city,length(city) from (select * from station order by length(city),city) where rownum=1
UNION
select city,length(city) from (select * from station order by length(city) desc,city) where rownum=1;
