Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Select the records which have a max() of a field
Message
 
 
To
17/11/2002 23:01:01
Dragan Nedeljkovich (Online)
Now officially retired
Zrenjanin, Serbia
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00723707
Message ID:
00723736
Views:
9
If VFP supported correlated queries beyond simple ones, something like this would work
SELECT * ;
	FROM curFirst cf1 ;
	WHERE Field3 ;
		IN ( SELECT MAX(field3) 
				FROM curFirst cf2 ;
				WHERE cf2.field1 = cf1.field1 ;
				AND cf2.field2 = cf1.field2);
	INTO CURSOR crsResult
>>You can do this in two steps
SELECT field1, field2, MAX(field3) AS max3 ;
>>  FROM curFirst ;
>>  group by Field1, Field2 ;
>>  INTO CURSOR crsTemp
>>
>>SELECT * ;
>>	FROM curFirst  ;
>>	WHERE STR(Field1) + STR(Field2) + STR(Field3) ;
>>		IN ( SELECT STR(Field1) + STR(Field2) + STR(max3)
>>				FROM crsTemp );
>>	INTO CURSOR crsResult
>
>Which is what I did, in a slightly different manner on the second one, using a join. I thought there was a way to do it in a single select - and it almost worked, except it retrieved partial set of records only.
--sb--
Previous
Reply
Map
View

Click here to load this message in the networking platform