Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with an SQL Statement

Status
Not open for further replies.

waynedp

Programmer
Jan 12, 2017
13
US
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
 
If I understand your question, you just need to add the check for zipcode to the WHERE clause of the subquery.

Code:
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

 
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
 
>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:

Code:
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top