>I need to retrieve the top 2 most current rows (using date) for each group (A, B, C). I have created some sample data and the expected results. Anyone got a solution?
>
>

>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}, 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 group,  date  ;
> FROM temp t1 ;
> WHERE pk IN ;
> (SELECT TOP 2 pk FROM temp t2 ;
>    WHERE t2.group = t1.group ;
>    ORDER BY date desc)
>*-- Get Queries of this type are not supported
>*-- should get back
>group date
>A    05/01/2005
>A    06/01/2005
>B    02/01/2005
>B    03/01/2005
>C    05/01/2005
>C    06/01/2005
>

This is the definitive solution:

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/05/01}, 3 )
INSERT INTO temp (group, date, value) values('A', {^2005/05/01}, 4 )
INSERT INTO temp (group, date, value) values('A', {^2005/06/01}, 5 )
INSERT INTO temp (group, date, value) values('A', {^2005/05/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}, 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 )

* exactly <=3; with the same date, 
* it returns the last inserted (with increasing primary key)

SELECT temp.* FROM temp;
JOIN (SELECT t1.pk FROM temp t1 ;
			 JOIN temp t2 ;
        ON  t2.group = t1.group ;
           AND (t2.date > t1.date OR t2.date = t1.date AND t2.pk => t1.pk);
	GROUP BY 1 HAVING COUNT(*)<=3) tg ON tg.pk=temp.pk