Need query to show duplicates only-please help

Need query to show duplicates only-please help

Post by andy USFW » Thu, 29 Aug 2002 20:07:36



Hi all, I have a table that I need to query
to show only duplicate DocNums and an associated field
called DocType.

Example of desired output:

DocNum    DocType
-------- ----------
92-155     TOP
92-155     ROP
92-155     HOR
02-999     TOP
02-999     ROP

The following was suggested on another site, but will not
work with SQL Server:

SELECT     DocNum, DocType
FROM         leDocs
WHERE     (docnum, doctype) IN
                          (SELECT     docnum, doctype
                            FROM          ledocs
                            GROUP BY docnum, doctype
                            HAVING      COUNT(*) > 1)
ORDER BY docnum, doctype

Any help will be appreciated, thanks, Andy

 
 
 

Need query to show duplicates only-please help

Post by chri » Thu, 29 Aug 2002 20:10:53


SELECT MyColumn
FROM MyTable
GROUP BY MyColumn
HAVING COUNT(*) > 1


Quote:> Hi all, I have a table that I need to query
> to show only duplicate DocNums and an associated field
> called DocType.

> Example of desired output:

> DocNum    DocType
> -------- ----------
> 92-155     TOP
> 92-155     ROP
> 92-155     HOR
> 02-999     TOP
> 02-999     ROP

> The following was suggested on another site, but will not
> work with SQL Server:

> SELECT     DocNum, DocType
> FROM         leDocs
> WHERE     (docnum, doctype) IN
>                           (SELECT     docnum, doctype
>                             FROM          ledocs
>                             GROUP BY docnum, doctype
>                             HAVING      COUNT(*) > 1)
> ORDER BY docnum, doctype

> Any help will be appreciated, thanks, Andy


 
 
 

Need query to show duplicates only-please help

Post by Gerard Esteba » Thu, 29 Aug 2002 20:26:02


How about the this?

SELECT DocNum, DocType
FROM leDocs
WHERE DocNum IN (SELECT DocNum
         FROM leDocs
         GROUP BY DocNum
         HAVING COUNT(*) > 1)

Thanks,
Gerard


Quote:> Hi all, I have a table that I need to query
> to show only duplicate DocNums and an associated field
> called DocType.

> Example of desired output:

> DocNum    DocType
> -------- ----------
> 92-155     TOP
> 92-155     ROP
> 92-155     HOR
> 02-999     TOP
> 02-999     ROP

> The following was suggested on another site, but will not
> work with SQL Server:

> SELECT     DocNum, DocType
> FROM         leDocs
> WHERE     (docnum, doctype) IN
>                           (SELECT     docnum, doctype
>                             FROM          ledocs
>                             GROUP BY docnum, doctype
>                             HAVING      COUNT(*) > 1)
> ORDER BY docnum, doctype

> Any help will be appreciated, thanks, Andy

 
 
 

Need query to show duplicates only-please help

Post by andy USFW » Thu, 29 Aug 2002 20:35:43


Thanks to Gerard. I really appreciate it, Andy
>-----Original Message-----
>How about the this?

>SELECT DocNum, DocType
>FROM leDocs
>WHERE DocNum IN (SELECT DocNum
>         FROM leDocs
>         GROUP BY DocNum
>         HAVING COUNT(*) > 1)

>Thanks,
>Gerard



>> Hi all, I have a table that I need to query
>> to show only duplicate DocNums and an associated field
>> called DocType.

>> Example of desired output:

>> DocNum    DocType
>> -------- ----------
>> 92-155     TOP
>> 92-155     ROP
>> 92-155     HOR
>> 02-999     TOP
>> 02-999     ROP

>> The following was suggested on another site, but will
not
>> work with SQL Server:

>> SELECT     DocNum, DocType
>> FROM         leDocs
>> WHERE     (docnum, doctype) IN
>>                           (SELECT     docnum, doctype
>>                             FROM          ledocs
>>                             GROUP BY docnum, doctype
>>                             HAVING      COUNT(*) > 1)
>> ORDER BY docnum, doctype

>> Any help will be appreciated, thanks, Andy

>.

 
 
 

1. Need to query for duplicates only-please help

Hi all, I have a table that I need to query
to show only duplicate DocNums and an associated field
called DocType. Please help.

Example of desired output:

DocNum    DocType
-------- ----------
92-155     TOP
92-155     ROP
92-155     HOR
02-999     TOP
02-999     ROP

The following was suggested on another site, but will not
work with SQL Server:

SELECT     DocNum, DocType
FROM         leDocs
WHERE     (docnum, doctype) IN
                          (SELECT     docnum, doctype
                            FROM          ledocs
                            GROUP BY docnum, doctype
                            HAVING      COUNT(*) > 1)
ORDER BY docnum, doctype

Any help will be appreciated, thanks, Andy

2. US,MN,Minn.:Oracle Applications Technical Lead

3. Please help! Show only duplicates

4. SQL Server 7.0

5. Please help with this "Duplicates" query

6. Problem migrating 7.2.3 to 7.3.3

7. APPROACH QUERY-PLEASE PLEASE PLEASE PLEASE HELP

8. Job notification email customization

9. HELP PLEASE: URGENT: Need help forming a multi table query

10. APPROACH QUERY-PLEASE PLEASE PLEASE HELP

11. Please, Please, Please I need help working with dates

12. Please Please help !!! Crystal Report Help Needed

13. I need help in designing a school database Please Please help