MAX for varchar or character expresions or strings

[DeeptiChand]

Say i have a COLUMN name varchar(20)

NAME:
----
john
john/a
john/b
john/a/a

------

I want to find the LONGEST NAME in the column and I am trying to use

: Select MAX(name) from tablename

SQL 2000 gives the max as per alphabetical order and in the above example the MAX is john/b

IS there any direct SQL statement to get the MAX name where MAX refers to the LONGEST in terms of length .


I have been able to do it with 2 sql statements where i first find the longest length and then give a query where i match the length..But is there any simpler way??

[MAK]

use tempdb

create table x1 (name varchar(100))
insert into x1 select 'john'
insert into x1 select 'john/a'
insert into x1 select 'john/b'
insert into x1 select 'john/a/a'


select * from x1 where len(name) = (select max(len(name)) from x1)

or

select top 1 name, max(len(name)) from x1 group by name order by len(name) desc

or

select name from (
select top 1 name, max(len(name)) as len from x1 group by name order by len(name) desc) as x

[MAK]

select top 1 name from x1 order by len(name) desc

or
select top 1 name from x1 order by datalength(name) desc


this would be your best bet