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