Fabio,
I am afraid, it does not work as desired (and I cannot suggest anything else). First ADD a couple of additional records as I did in your code below.
Let us suppose we need to gather top 3 for each group order by date, value. Then one may use this:
ln=3
SELECT TOP ln * FROM temp WHERE group="A" ORDER BY date desc, value DESC INTO CURSOR _1
SELECT TOP ln * FROM temp WHERE group="B" ORDER BY date desc, value DESC INTO CURSOR _2
SELECT TOP ln * FROM temp WHERE group="C" ORDER BY date desc, value DESC INTO CURSOR _3
SELECT * FROM _1;
union;
SELECT * FROM _2;
union;
SELECT * FROM _3;
into CURSOR _4;
order BY 2,3 desc,4 desc
However your JOHN statement gives only 2 for "C" group. You may test for ln=4, 5 etc. to have more results reproducing this behavior
>
>this is more general ( on VFP9 )
>
>CREATE CURSOR temp (pk I autoinc, group c(1), date d, value i)
>INSERT INTO temp (group, date, value) values('A', {^2005/01/01}, 1 )
>INSERT INTO temp (group, date, value) values('A', {^2005/02/01}, 2 )
>INSERT INTO temp (group, date, value) values('A', {^2005/03/01}, 3 )
>INSERT INTO temp (group, date, value) values('A', {^2005/04/01}, 4 )
>INSERT INTO temp (group, date, value) values('A', {^2005/05/01}, 5 )
>INSERT INTO temp (group, date, value) values('A', {^2005/06/01}, 6 )
>INSERT INTO temp (group, date, value) values('B', {^2005/01/01}, 1 )
>INSERT INTO temp (group, date, value) values('B', {^2005/02/01}, 2 )
>INSERT INTO temp (group, date, value) values('B', {^2005/03/01}, 3 )
INSERT INTO temp (group, date, value) values('C', {^2005/04/01}, 1 )
INSERT INTO temp (group, date, value) values('C', {^2005/05/01}, 2 )
INSERT INTO temp (group, date, value) values('C', {^2005/06/01}, 3 )
>INSERT INTO temp (group, date, value) values('C', {^2005/04/01}, 4 )
>INSERT INTO temp (group, date, value) values('C', {^2005/05/01}, 5 )
>INSERT INTO temp (group, date, value) values('C', {^2005/06/01}, 6 )
>
>SELECT temp.* FROM temp;
>JOIN (SELECT t1.pk FROM temp t1 ;
> JOIN temp t2 ON t2.group = t1.group AND t2.date >= t1.date;
> GROUP BY 1 HAVING COUNT(*)<=3) tg ON tg.pk=temp.pk
>
>
