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