>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

As you noticed, a GROUP clause generates a single row per group, but you want at most 3 rows. Maybe someone else can give you a shorter answer, but I believe you can't do this in a single SQL statement. So, this is what I propose, taking off from your code:

>

* 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

* Now, instead of:
* Get top 3 per cGroup
* Select top 3 thedbf.dDATE, thedbf.cGroup FROM thedbf order by 1 descend GROUP BY thedbf.cGroup

* 1) fetch the groups available
select thedbf.cGroup from thedbf group by thedbf.cGroup into cursor tmpGroups

* 2) for each of these groups
scan
   lcGroup = tmpGroups.cGroup

   * 3) if it is the first group create the first version of the result cursor
   if recno()=1
      Select top 3 thedbf.dDATE, thedbf.cGroup FROM thedbf where thedbf.cGroup=lcGroup order by 1 descend into cursor tmpTop3
   else

      * 4) if not, add the rows for this group to the previous state of the result
      Select top 3 thedbf.dDATE, thedbf.cGroup FROM thedbf where thedbf.cGroup=lcGroup order by 1 descend into cursor tmpAdded

      select * from tmpAdded ;
      union ;
      select * from tmpTop3 ;
      into cursor tmpNewTop

      * 5) step over caching issues...
      select * from tmpNewTop into cursor tmpTop3
   endif

   * 6) go to next group
   select tmpGroups
endscan

* 7) the final version of the result cursor
if reccount("tmpGroups")>0
   select * from tmpTop3 order by 1 descend
endif

bye