Hi Cyril,
I think you must do 2 commands.
1. Get the ID of the Top whatever.
2. SELECT AVG(Amount) AS Average FROM x, Result WHERE ID = Result.ID
>Hi Charlie
>
>It sure does. Despite your clear explanations, it still looks like magic to me<g>.
>
>Um, now I wonder if its possible to get the average of the top 3....
>
>Thanks to you and Antonio.
>Cyril.
>
>
>>Hi Cyril,
>>This article on my web site show how this can be done.
>>
http://home.kscable.com/cschreiner/OutFoxed11.html>>
>>
>>>Trying to use SELECT TOP. Have lots of records, like this:
>>>
>>> Group1, 01/01/2001
>>> Group1, 02/02/2001
>>> Group1, 03/03/2001
>>> Group1, 04/04/2001
>>>
>>> Group2, 01/01/2001
>>> etc
>>>
>>>I want to get the latest three records per group. Can't get the SQL right. This doesnt't do it:-
>>> Select top 3 thedbf.dDATE, thedbf.cGroup FROM thedbf order by 1 descend GROUP BY thedbf.cGroup
>>>
>>>Below is a little test program.
>>>Can you help?
>>>TIA
>>>Cyril.
>>>
>>>
>>>* Make sample data
>>>Use in select('thedbf')
>>>Create cursor thedbf (cGroup C(10), dDATE D)
>>>
>>>For iigroup = 1 to 9
>>> ciigroup = ltrim(str(iigroup))
>>> For ii = 1 to 9
>>> cii = '0'+ ltrim(str(ii))
>>> Insert into thedbf (cGroup, dDATE) values (ciigroup, ctod(cii+'/'+cii+'/2001'))
>>> Endfor
>>>Endfor
>>>*brow
>>>
>>>* Get top 3 per cGroup
>>>Select top 3 thedbf.dDATE, thedbf.cGroup FROM thedbf order by 1 descend GROUP BY thedbf.cGroup
Charlie