Hi Erick,
thanks for your response.
Possibly I do express myself wrong.
The question is:
The records for the last (IOW greatest) five dates per customer. The value is in no way bound to date.
The date field is in no way ordered or the date will
follow any rule. There may be 1 to n records per user. Every day may exist only once per customer. (customer+date may create a candidate key).
Agnes
>Hi 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
>>Wouldn't be value's customer B from 4 until 8? (you putted 3 until 7)
>If your answer is yes. Try:
>
>SELECT mA.*;
> FROM myTable mA;
> , (SELECT customer, MAX(value) maxValue;
> FROM myTable;
> GROUP BY customer;
> ) mB;
> WHERE mA.customer = mB.customer;
> AND mA.value > mB.maxValue - 5;
> ORDER BY mA.customer, mA.value
>
>Good luck!
Words are given to man to enable him to conceal his true feelings.
Charles Maurice de Talleyrand-Périgord
Weeks of programming can save you hours of planning.
OffThere is no place like [::1]