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 descHowever your JOHN statement gives only 2 for "C" group. You may test for ln=4, 5 etc. to have more results reproducing this behavior
>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 ) * add records 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 > >