Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Can this be done with SQL statement
Message
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Miscellaneous
Thread ID:
00728373
Message ID:
00728413
Views:
13
Arrg, forget my previous reply it doesn't work...
This should work better, but you will get duplicates if the max(fldb) in not unique.
Create Cursor Temp ( flda C(1), fldb B, fldc C(30), fldd C(10), ID B )
Insert Into Temp (flda , fldb , fldc , fldd, ID ) Values ( "A", 100, "aTest100-1", "aTest100-2", 1 )
Insert Into Temp (flda , fldb , fldc , fldd, ID ) Values ( "A", 300, "aTest300-1", "aTest300-2", 2 )
Insert Into Temp (flda , fldb , fldc , fldd, ID ) Values ( "A", 150, "aTest150-1", "aTest150-2", 3 )
Insert Into Temp (flda , fldb , fldc , fldd, ID ) Values ( "B", 500, "bTest500-1", "bTest100-2", 4 )
Insert Into Temp (flda , fldb , fldc , fldd, ID ) Values ( "B", 800, "bTest800-1", "bTest300-2", 5 )
Insert Into Temp (flda , fldb , fldc , fldd, ID ) Values ( "B", 600, "bTest600-1", "bTest150-2", 6 )

Select * From Temp Where fldb IN ;
	( Select Max(fldb) From Temp Group By flda  ) ;
	Into Cursor Temp2
Browse
>Plamen,
>On further investigation, it is not doing exactly as I want, if I do a select *. I want the Highest Flda RECORD returned
>
>Flda Fldb Fldc Fldd Flde etc
>A 100
>A 300
>A 150
>B 500
>B 800
>B 600
>
>If I Select SELECT *, MAX(Fldb) AS C FROM TempFile GROUP BY C, it returns
>
>Flda Fldb C
>A 150 300 Record no. 3 from File above
>B 600 800 Record no. 6 from File above
>
>What I want returned is:
>A 300 Record no. 2 above
>B 800 Record no. 5 above
>
>The file above is just a sample for illustration purposes. I am reaaly in terested in the other fields that are in the file, and I need the record whch has the highest FldB value returned , for any of the records grouped by Flda.
>
>Regards
>Gerard
>
>
>
>
>
>
>
>
>
>>Plamen,
>>Thank you very much for your reply which works great
>>
>>Gerard
>>
>>
Previous
Reply
Map
View

Click here to load this message in the networking platform