>>Hi All,
>>i've custNotes table and wantto query last 3 notes from each customer from custNotes table , below codes doesn't solve my need :(
>>
>>where is the problem ?
>>
>>
>>SELECT TOP 3 date,custId,notes FROM custNotes ORDER BY date,custNotes DESC
>>
>
>If you want it for each customer, TOP 3 isn't going to do it for you. The solution I know comes from Fabio Lunardon and does like this:
>
>
>SELECT CustNotes.Date, CustNotes.CustID, CustNotes.Notes ;
> FROM CustNotes ;
> JOIN (SELECT cna.Date, cna.CustID ;
> FROM CustNotes cna ;
> JOIN CustNotes cnb ;
> ON cna.CustID = cnb.CustID ;
> AND cna.Date <= cnb.Date ;
> GROUP BY 1, 2 ;
> HAVING CNT(*) <= 3 ) MostRecent ;
> ON CustNotes.Date = MostRecent.Date ;
> AND CustNotes.CustID = MostRecent.CustID ;
> INTO CURSOR MostRecentNotes
>
>
>The inner query finds the three most recent records for each customer and the outer then pulls the data you need. Note that if you could have more than one record per customer per date, you'll need to find a way to distinguish them.
>
>Tamar
Thanks Tamar it working fine :)