Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
The max value of a given group
Message
 
 
To
18/03/2004 15:35:03
Erick Miranda
Formata Data Business - Grupo Linx
Contagem, Brazil
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00887108
Message ID:
00887729
Views:
9
Eric,

Your query may produce incorrect result because it doesn't specify from wich record name field comes from. It may or may not come from the record with MAX() value of age in a group. Here's a simple test that shows the problem.
CREATE CURSOR test (Name C(20), Family C(2), Level C(1), Age N(3))
INSERT INTO test VALUES( "", "01", "1", 18)
INSERT INTO test VALUES( "", "01", "1", 15)
INSERT INTO test VALUES( "", "01", "1", 10)
REPLACE ALL Name WITH "Name-" + Family + "-" + Level + "-" + TRANSFORM(Age)

* Your query
Select Name, Family, Level, Max(Age) Age ;
	From test ;
	Group By Family, Level


* Data
Name          Family Level Age
Name-01-1-18  01     1      18
Name-01-1-15  01     1      15
Name-01-1-10  01     1      10

* Result 
Name          Family Level Age
Name-01-1-10  01     1      18
As you can see the 'Name' was pulled from the last physical record in the group not from the record with MAX(age).
BTW, VFP8 will generate error on your query because it's result is ambiguous.

>I did the test in VFP5 and work well!
>In any case, thanks.
--sb--
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform