Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Chad Lee
  Where is Chad Lee?
 Marshall
 Texas - United States
 Chad Lee
 To: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: Need help with Distinct or Group By
Thread ID: 228143 Message ID: 228425 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Monday, May 18, 2009 8:03:10 PM         
   


> > I have a table with repeated names in it. The rows are not duplicated, the table just might have more than one record for a particular person. What I need is a query to see how many specific distinct people have data in the table.
> >
> > For instance:
> >
> > CREATE CURSOR curTesty (fname c(20), lname c(20), id int, item c(20))
> >
> > INSERT INTO curTesty VALUES( "John", "Jones",1,"Radio")
> > INSERT INTO curTesty VALUES( "John", "Jones",1,"Toothbrush")
> > INSERT INTO curTesty VALUES( "Bill", "Jones",2,"Radio")
> > INSERT INTO curTesty VALUES( "Bill", "Jones",2,"lamp")
> > INSERT INTO curTesty VALUES( "Bill", "Jones",2,"pencil")
> > INSERT INTO curTesty VALUES( "Dave", "Smith",3,"Radio")
> >
> > what I need to see is,
> >
> > John , Jones,1, radio
> > Bill, Jones,2, Radio
> > Dave, Smith,3, Radio
> >
> > There are 80 fields in my original table, and I would need all 80 fields to be copied for each row.
>
> You happen to havev posted the very same probelm as Dan Dunn in the hread ### AN INVALID ID WAS ENTERED ###.
> Here's my reply to Dan and you.
>
Using Xbase code
> INDEX ON company TAG temp UNIQUE 
> COPY TO newtable 
> 
> 
> Doing it with SQL is trickier; the result you get with the Xbase way is too random in the eyes of SQL theorists.
> It is possible though if we add a separate primary, or candidate key.
> 
> ALTER TABLE Companies ADD COLUMN key Int 
> UPDATE Companies SET key = RECNO()
> SELECT T1.* FROM Companies AS T1 JOIN ;
>  (SELECT MAX(key) AS key, company FROM Companies GROUP BY company ) As T2;
>  ON T1.key=T2.key INTO CURSOR Q1  

>
>
> -Anders


Wow, I will have to try that.

ENTIRE THREAD

Need help with Distinct or Group By Posted by Chad Lee @ 5/15/2009 8:58:29 PM
RE: Need help with Distinct or Group By Posted by Jon Goad @ 5/15/2009 9:09:19 PM
RE: Need help with Distinct or Group By Posted by Chad Lee @ 5/15/2009 10:25:21 PM
RE: Need help with Distinct or Group By Posted by Stefan Wuebbe @ 5/15/2009 10:37:35 PM
RE: Need help with Distinct or Group By Posted by Chad Lee @ 5/15/2009 10:54:19 PM
RE: Need help with Distinct or Group By Posted by Ammar Hadi @ 5/16/2009 12:11:03 AM
RE: Need help with Distinct or Group By Posted by Chad Lee @ 5/18/2009 8:01:01 PM
RE: Need help with Distinct or Group By Posted by Ammar Hadi @ 5/18/2009 8:32:33 PM
RE: Need help with Distinct or Group By Posted by Anders Altberg @ 5/16/2009 12:54:00 AM
RE: Need help with Distinct or Group By Posted by Chad Lee @ 5/18/2009 8:03:10 PM