>Hi All,
>
>I have a table that holds a time tabes status per customer.
>
>I would like to create an SELCT SQL that gives me the 5 most recent records per customer
>
>like
>
>customer, date,value
>A,2007-01-01,1
>A,2007-01-02,2
>A,2007-01-03,3
>A,2007-01-04,4
>A,2007-01-05,5
>A,2007-01-06,6
>A,2007-01-07,7
>A,2007-01-08,8
>B,2007-01-02,2
>B,2007-01-03,3
>B,2007-01-04,4
>B,2007-01-05,5
>B,2007-01-16,6
>B,2007-01-17,7
>B,2007-01-18,8
>C,2007-01-17,7
>C,2007-01-18,8
>
>Result set:
>A,2007-01-04,4
>A,2007-01-05,5
>A,2007-01-06,6
>A,2007-01-07,7
>A,2007-01-08,8
>B,2007-01-03,3
>B,2007-01-04,4
>B,2007-01-05,5
>B,2007-01-16,6
>B,2007-01-17,7
>C,2007-01-17,7
>C,2007-01-18,8
>
>
>Any idea how to achive this?
>
>Agnes

VFP's SQL engine is not as powered,
and a hugly solution can to be founded only.

CREATE CURSOR table (customer C(1), date D,value I)

INSERT INTO table VALUES ('A',DATE(2007,01,01),1)
INSERT INTO table VALUES ('A',DATE(2007,01,02),2)
INSERT INTO table VALUES ('A',DATE(2007,01,03),3)
INSERT INTO table VALUES ('A',DATE(2007,01,04),4)
INSERT INTO table VALUES ('A',DATE(2007,01,05),5)
INSERT INTO table VALUES ('A',DATE(2007,01,06),6)
INSERT INTO table VALUES ('A',DATE(2007,01,07),7)
INSERT INTO table VALUES ('A',DATE(2007,01,08),8)
INSERT INTO table VALUES ('B',DATE(2007,01,02),2)
INSERT INTO table VALUES ('B',DATE(2007,01,03),3)
INSERT INTO table VALUES ('B',DATE(2007,01,04),4)
INSERT INTO table VALUES ('B',DATE(2007,01,05),5)
INSERT INTO table VALUES ('B',DATE(2007,01,16),6)
INSERT INTO table VALUES ('B',DATE(2007,01,17),7)
INSERT INTO table VALUES ('B',DATE(2007,01,18),8)
INSERT INTO table VALUES ('C',DATE(2007,01,17),7)
INSERT INTO table VALUES ('C',DATE(2007,01,18),8)

* the correct solution, but ...
SELECT customer, date,value;
	FROM table T where T.date IN (SELECT TOP 5 date FROM table WHERE customer = T.customer ORDER BY date DESC)

* a long way solution, but ...
SELECT customer, date,value;
	FROM table T ;
	WHERE (SELECT COUNT(*) FROM table X WHERE X.customer = T.customer AND X.date >= T.date ) <=5

* another long solution
SELECT customer, date,value;
	, (SELECT COUNT(*) FROM table X WHERE X.customer = T.customer AND X.date >= T.date ) cn;
	FROM table T ;
	HAVING  cn<=5