The help system only states that TOP N is supported in a Non-Correlated Subquery, leaving one to assume that it is not supported in a correlated subquery.

Unfortunately from what I can see you need a correlated subquery to do this in a single SQL statement, but even if that was available I think it would be very inefficient. So in my opinion you would be better off to do something like this:

create cursor mytable (cgroup c(1), iscore i)
SELECT * FROM  mytable INTO CURSOR OutTable READWRITE 

FOR X = 1 TO 20
  cX = CHR(64+X)
  FOR Y = 1 TO 100
    INSERT INTO mytable (cgroup,iscore) VALUES (cX,Y)
  ENDFOR
ENDFOR

SELECT cgroup FROM mytable GROUP BY cgroup INTO CURSOR UCGroup 
SCAN
  SELECT TOP 20 PERCENT * FROM MyTable WHERE MyTable.cgroup = UCGroup.cgroup ORDER BY iscore;
    INTO CURSOR Temp 
  SELECT OutTable
  APPEND FROM DBF("Temp")
  USE IN Temp
  SELECT UCGroup 
ENDSCAN
USE IN UCGroup 

In the above example the query runs 20 times, an unoptimized correlated subquery if available would run the query 2000 times. So something like this seems more efficient to me.


>Hi,
>
>I have a VFP table with the following structure...
>
>idnum
>keyword
>cgroup
>iscore
>
>...I have one SQL statement that gets the top 20 percent (iscore) of all records (100 records returns 20), and this works fine...
>
>SELECT TOP 20 PERCENT,* FROM Table ORDER BY iscore
>
>I am now trying to get the top 20 percent (iscore) of each group (cgroup) and am struggling for the correct SQL syntax.
>
>Regards
>Doug Johnston