Need help with an SQL Statement

[waynedp]

Hi,


Current I am using the following statement to get the longest full name:

SELECT TOP 1 CITY FROM MYDB ORDER BY SQNCNMBR,
WHERE LEN(CITY) = (SELECT MAX(LEN(CITY)) FROM MYDB,
WHERE LEN(CITY)) > 0)


What I am trying also to add is a check for zipcode > 5


Please advice

 

[TamarGranor]

If I understand your question, you just need to add the check for zipcode to the WHERE clause of the subquery.

SELECT TOP 1 CITY FROM MYDB ORDER BY SQNCNMBR,
WHERE LEN(CITY) = (SELECT MAX(LEN(CITY)) FROM MYDB,
WHERE LEN(CITY)) > 0 AND LEN(Zip)>5)

Tamar

 

[DaveInIowa]

What if multiple cities have the same length?

 

[waynedp]

Yes, I did that before submitting my question, I am attach the length, but did not get any records, than I tried on another database and it worked. I submitted question because I thought my zip code length wasn't working. And Dave I am only selecting Top 1 for maximum length. Thanks guys

 

[Olaf Doschke]

>And Dave I am only selecting Top 1 for maximum length.

You haven't understood the implication Dave makes. While TOP 1 only returns one row (unless you'd use the WITH TIES option), if you have ties, you may still get two or more results with a query fetching the city with "the" longest name and getting, if this isn't a unique length:

declare @test as table (city varchar(10));
declare @longest as int;

insert into @test values ('Dublin'),('Leeds'),('London');

select top 1 @longest=len(city) from @test order by len(city) DESC
select city from @test where len(city) = @longest

So while the top length is only one value, there could still be many cities with the same name length.

Bye, Olaf.