How to get shortest and longest string from table?

Question

Suppose, I have a table Station in SQL Server:

|  ID  | Name |
+------+------+
|  1   | a    |
|  2   | b    |
|  3   | cc   |
|  4   | ddd  |
|  5   | eee  |

I want to retrieve shortest and Longest name along with length (in case of a tie, it will show alphabetically top 1) so, the output will be

|  a  |   1  |
+-----+------+
| ddd |   3  |

I tried this:

Select  
    Name, len(name) 
from 
    Station 
where 
    len(name) = (Select min(len(name)) from Station) 
union

Select  
    Name, len(name) 
from 
    Station 
where 
    len(name) = (Select max(len(name)) from Station)

But, I have to take the alphabetically first only which I am not able to do

Hasan Gholamali · Accepted Answer · 2018-06-10T11:01:56.393

1

SELECT *
FROM
    (SELECT TOP(1) *, LEN(name) as ln
    FROM Student
    ORDER BY ln, name
    UNION
    SELECT TOP(1) *, LEN(name) as ln
    FROM Student
    ORDER BY ln DESC, name) as tblMain

edited Jun 10 '18 at 11:01

answered Jun 10 '18 at 10:56

Hasan Gholamali

633
4
13

sorry, its not working saying-"Incorrect syntax near the keyword 'UNION'" – BlackCat Jun 10 '18 at 10:59
An `order by` that is part of a union is ignored (see [this question](https://stackoverflow.com/questions/842798/sql-using-top-1-in-union-query-with-order-by)), so I'm not sure this would work consistently – Andomar Jun 10 '18 at 11:09
your-welcome, the first one didn't work because of it is not possible to use two different `ORDER BY` in the `UNION` statement. check this link: https://blog.sqlauthority.com/2012/10/30/sql-server-union-all-and-order-by-how-to-order-table-separately-while-using-union-all/ – Hasan Gholamali Jun 10 '18 at 11:10

score 1 · Answer 2 · answered Jun 10 '18 at 11:01

select  *
from    (
        select  name
        ,       row_number() over (order by len(name), name) rn1
        ,       row_number() over (order by len(name) desc, name) rn2
        from    student
        ) sub
where   rn1 = 1  -- Shortest name
        or rn2 = 1  -- Longest name

score 1 · Answer 3 · answered Jun 10 '18 at 11:10

Your method would work with a slight tweak:

Select  min(Name) as name, len(name)
from Station 
where len(name) = (Select min(len(name)) from Station) 
group by len(name)
union all
Select min(Name) as name, len(name)
from Station 
where len(name) = (Select max(len(name)) from Station) 
group by len(name);

or:

Select min(Name) as name, min(len(name))
from Station 
where len(name) = (Select min(len(name)) from Station) 
union all
Select min(Name) as name, min(len(name))
from Station 
where len(name) = (Select max(len(name)) from Station) ;

That is, by aggregation you get one row.

How to get shortest and longest string from table?

3 Answers3