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