Hi Fabio,

nice to have your assistance again.

Do I read "but ..." that VFP will not work this way?

Agnes
>>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
>