Just wanted to thank you for your help and ask you if you know of some books/literature/web sites where I can learn more about SELECT - SQL commands.


>>QUESTION #1:
>>I have been playing with the SELECT command you showed me and I don't understand why the COUNT(*) and the HAVING clause are needed in it. I get the same results (unique records) if I do the following two commands:
>>
>>SELECT count(*),* FROM mytable GROUP BY field1,field2,field3 HAVING COUNT(*)>1
>>-or-
>>SELECT * FROM mytable GROUP BY field1,field2,field3
>>
>>Do you think this is right or am I missing something?
>>
>
>Yes, you're missing the fact that Eric's query only returns records that have duplication within the grouping. Just the ones you have to do something about IOW...
>
>IOW, after the query, you have all the non-duplicate records with a COUNT(*) of 1, and anything with more has duplicates.
>
>>QUESTION #2:
>>About to copy the records... I am looking for a SELECT command to give me the DUPLICATE records also. The first one was to display the unique records, now I want another command to display the duplicate ones.
>>
>
>First:
>
>

SELECT COUNT(*) AS NumOccurs, * FROM MyTable ;
>   INTO CURSOR AllCounts ;
>   GROUP BY <i> field list </i> && This is all distinct records
>
>SELECT * From AllCounts ;
>  INTO CURSOR UniqueRecs ;
> WHERE NumOccurs = 1 && non-dups
>
>SELECT * From AllCounts ;
>  INTO CURSOR RecsWithDupes ;
> WHERE NumOccurs > 1 && at least one dupe