Foxpro SQL Help

[waynedp]

I am trying to obtain the longest full name with a zip+4


SELECT TOP 1 * FROM waynedb ORDER BY FULLNAME WHERE LEN(RTRIM(FULLNAME)) = (SELECT MAX(LEN(RTRIM(FULLNAME))) FROM waynedb WHERE LEN(RTRIM(FULLNAME)) > 0 AND LEN(RTRIM(ZIP4)) > 9)


I am receiving records without zip+4

 

[Mike Lewis]

Yes, that's exactly what you are asking for.

Your inner SELECT is giving one row for each name where the name is of max length and the Zip4 exceeds nine characters. That's what you want. But your outer SELECT is then just asking for rows where the name is of the length in question, without regard to the Zip4.

So, say the longest name is 10 chars, and that occurs in 2 records; one record has Zip4 > 9 and the other doesn't. The inner query will simply return 10. The outer query will find all 10-char names, regardless of the length of Zip4.

It's just a matter of moving the Zip4 test to the outer level:

SELECT TOP 1 * FROM waynedb ;
  ORDER BY FULLNAME ;
  WHERE LEN(RTRIM(FULLNAME)) = ;
    (SELECT MAX(LEN(RTRIM(FULLNAME))) FROM waynedb WHERE LEN(RTRIM(FULLNAME)) > 0[b])[/b] AND LEN(RTRIM(ZIP4)) > 9

In other words, just move the final closing parenthesis to just after the > 0.

I haven't tested this, so cannot guarantee it's correct, but I am fairly sure it's something along these lines.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Also, you say you want the longest name. But your TOP 1 clause is giving you the first name in alphabetical order that is equal in length to the longest name. Is that what you want - given that you might have many names of that length?

Maybe something like this would work better:

SELECT top 1 Fullname, LEN(RTRIM(Fullname)) AS NameLen ;
  FROM waynedb ;
  WHERE LEN(RTRIM(Zip4)) > 9 ;
  ORDER BY 2 DESC

If you are using VFP 8 or below, that will give you all the rows where the name is of the max length and the Zip4 requirement is met (in other words, there is an implicit WITH TIES clause). In VFP 9, you will get just one of the names in question. If you prefer the VFP 8 behaviour, you can SET ENGINEBEHAVIOR 80, but be aware that might have other implications that don't want.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You also might want the longest name with a minimum length zip4 value, but then you need that clause in the inner AND outer query. The real problem is length of a name is not a unique identifer you can use to get the record where that length is found, most probably the max length is present in multiple records.

Bye, Olaf.

 

[Olaf Doschke]

I would suggest you do this:

SELECT TOP 1 LEN(RTRIM(FULLNAME)) as NameLength,* FROM waynedb WHERE LEN(RTRIM(ZIP4)) > 9 ORDER BY NameLength DESC, FULLNAME

Much simpler, isn't it?

CREATE CURSOR waynedb (fullname C(50), zip4 char(11))

INSERT INTO waynedb VALUES ('long name','1234567890')
INSERT INTO waynedb VALUES ('longer name','1234567890')
INSERT INTO waynedb VALUES ('same length','10987654321')
INSERT INTO waynedb VALUES ('longest name','123456789') && but zip4 shorter than 10

SELECT TOP 1 LEN(RTRIM(FULLNAME)) as NameLength,* FROM waynedb WHERE LEN(RTRIM(ZIP4)) > 9 ORDER BY NameLength DESC, FULLNAME

By the way as I searched zip+4 codes are at max length 9. not longer. If you try to identify problematic zip+4 codes, then why for the longest name? Are you storing them with dash after the 5th digit? Why?

Though Mike mention VFPs differing enginebehaviour in regard of ties, your original problem surely is the way you put the conditions, not a VFP specific SQL difference. You get the longest full name from the subset of records with zip+4 longer than 9, but then in the outer query restart from all records with that fullname length.

Bye, Olaf.

 

[Mike Lewis]

By the way as I searched zip+4 codes are at max length 9. not longer.

When I first saw the question, I assumed that a row "with a zip+4" means a row whose trimmed Zip4 field has more than nine characters. Hence Wayne's test for [tt]LEN(RTRIM(Zip4)) > 9[/tt].

But:

A ZIP+4 Code uses the basic five-digit code plus four additional digits

In that case, the test should be for [tt]LEN(RTRIM(Zip4)) = 9[/tt]. Olaf points out that you might be storing a dash after the 5th digit, in which case you should test for [tt]>= 9[/tt] (but I agree that there is no reason to store the dash). But simply testing for [tt]> 9[/tt] is almost certainly wrong.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Depends on what you want to test. Stored with a dash zips having extra digits will at least have length 5+1+1=7. Full zip+4 with dash have length 10 and then >9 is finding those maxed out zips. The original problem still is not limiting the final outer filtering to long zip4s again, so the top record found in fullname order likely will have a shorter zip4.

This is what you'd need

[highlight #4E9A06]SELECT TOP 1 * [b]FROM waynedb[/b] ORDER BY FULLNAME WHERE LEN(RTRIM(FULLNAME)) = [/highlight]([highlight #8AE234]SELECT MAX(LEN(RTRIM(FULLNAME))) [b]FROM waynedb[/b] WHERE LEN(RTRIM(FULLNAME)) > 0 AND LEN(RTRIM(ZIP4)) > 9[/highlight]) [highlight #4E9A06]AND LEN(RTRIM(ZIP4)) > 9[/highlight]

I find it easier to sort by LEN(RTRIM(FULLNAME)) descending, then the record with max length is top 1. Much easier than repeating the where clause, especially if there would be more conditions. The secondary sorting by fullname in the simple order by solution will give the first in alphabetical order within the first records having that max length, if there are more. And the where clause is overall without a subquery. Most easy thing to do.

By the way I don't get results with tie. I would need same name, perhaps, to get with ties...no, that also only fetches one result row. ANSI setting also has no influence on the ties. Ah, I forgot to change enginebehavior. Yes, 80 is with ties, 90 without ties.

Bye, Olaf.

 

[Mike Lewis]

Regarding ties, the situation is as follows:

8.0 and below: Behaves as if WITH TIES is always present.

9.0: Behaves is if WITH TIES is never present.

If you want to get rid of ties in 8.0 and below, group the query on the same fields as its ORDER BY:

* VFP 8.0

CREATE CURSOR test (f1 i)
INSERT INTO test (f1) VALUES (1)
INSERT INTO test (f1) VALUES (1)
INSERT INTO test (f1) VALUES (2)

SELECT TOP 1 f1 FROM test ORDER BY f1 
  && returns 2 rows

SELECT TOP 1 f1 FROM test ORDER BY f1 [b]GROUP BY f1[/b]
  && returns 1 row

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Thanks Mike, I saw it already. But good idea to use the GROUP BY.

I'm not tempted to go for enginebehavior 80 having VFP9, not only for that reason. It would only strike, if queries I post here are not working in VFP8 because of these slight differences, and thus good to know.

Bye, Olaf.