Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Patrick Danso
  Where is Patrick Danso?
 Accra
 Ghana
 Patrick Danso
 Tags
Subject: RE: Selection Record per Member
Thread ID: 345570 Message ID: 345571 # Views: 68 # Ratings: 1
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Thursday, May 24, 2012 2:47:07 PM         
   


> Hi,
>
> I have a table with records of member’s payments. So this table have all payments of all members. I want to select ONLY one record per member from this table. How can i do this?
> Thank you

As I understand it is something like:

memberID, payDate, payAmount
1, 2012/1/1, 100
1, 2012/2/1, 120
2, 2012/1/1, 150
2, 2012/2/1, 130
1, 2012/3/1, 170


You first need to decide what would be the content for columns like payDate, payAmount be (from which row they come from)?

If you don't care one very simple solution is to use unique index. ie:

index on memberID tag dummy unique
browse && or copy to etc. only the rows with first occurences per memberID is shown


Or you might want to get the row with the latest payment date (assuming it is a datetime value and there is only a single payment at a given datetime and memberID) then:


select t1.* ;
 from myPayments t1 ;
 inner join ;
 (select memberID, max(payDate) as maxDate from myPayments group by 1) t2 ;
 on t1.memberID = t2.memberID and t1.payDate = t2.payDate


There are other scenarios too but first you need to explain what youre data really looks like and how you would select the single row per member (don't forget to provide some data using SampleDataToText.prg from FAQ section).



Cetin Basoz

.Net has got better.Think about moving - check my blog:
My Blog
Blog (mirror) - sounds to be down


Support Wikipedia

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