>>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?
>>
>>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
>>
>
>The problem is that you can't use TOP n in a correlated subquery. I'll keep pondering on this and see if I can come up with a solution.
>
>Tamar
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}, 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
