Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Tamar Granor
  Where is Tamar Granor?
 Elkins Park
 Pennsylvania - United States
 Tamar Granor
 To: Patrick Danso
  Where is Patrick Danso?
 Accra
 Ghana
 Patrick Danso
 Tags
Subject: RE: Selection Record per Member
Thread ID: 345570 Message ID: 345606 # Views: 42 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Thursday, May 24, 2012 10:18:10 PM         
   


> Payments table;
> MemberID date Name
> 0001 01/01/12 Hook
> 0052 01/01/12 James
> 0003 13/02/12 Fred
> 0001 23/02/12 Hook
> 0011 25/02/12 Pat
> 0052 13/03/12 Peter
>
> I want my result as below
> Result table;
> MemberID date Name
> 0001 01/01/12 Hook
> 0052 01/01/12 James
> 0003 13/02/12 Fred
> 0011 25/02/12 Pat

So you want the earliest record for each name. Try this (untested):

SELECT * ;
  FROM Payments ;
    JOIN (SELECT MemberID, MIN(Date) as dFirst ;
            FROM Payments LP ;
            GROUP BY MemberID) FirstPayment ;
      ON Payments.MemberID = FirstPayment.MemberID ;
       AND Payments.Date = FirstPayment.dFirst ;
  INTO CURSOR Result


If you can have two payments for the same person on the same date, you'll have to do a little more work.
Tamar

ENTIRE THREAD

Selection Record per Member Posted by Patrick Danso @ 5/24/2012 2:17:33 PM
RE: Selection Record per Member Posted by Cetin Basoz @ 5/24/2012 2:47:07 PM
RE: Selection Record per Member Posted by Anil Sharma @ 5/24/2012 2:58:11 PM
RE: Selection Record per Member Posted by Patrick Danso @ 5/24/2012 4:23:12 PM
RE: Selection Record per Member Posted by Tamar Granor @ 5/24/2012 10:18:10 PM
RE: Selection Record per Member Posted by Patrick Danso @ 5/25/2012 1:22:39 PM
RE: Selection Record per Member Posted by Anders Altberg @ 5/26/2012 2:45:14 PM