Need to query for duplicates only-please help

Need to query for duplicates only-please help

Post by andy USFW » Fri, 30 Aug 2002 03:03:49



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

 
 
 

Need to query for duplicates only-please help

Post by Mikhail Berlyan » Fri, 30 Aug 2002 03:12:15


select  DocNum, DocType
from  leDocs
where  DocNum in
(
select  DocNum
from  leDocs
group by DocNum
having  count(*) > 1
)

--
Mikhail Berlyant
Data Integrator, Data Systems
Launch Your Yahoo!Music Experience  http://launch.yahoo.com
Brainbench MVP for Visual Basic   www.brainbench.com


Quote:> 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


 
 
 

Need to query for duplicates only-please help

Post by Princ » Fri, 30 Aug 2002 03:12:19


SELECT     docnum, doctype,count(*)
                             FROM          ledocs
                          GROUP BY docnum, doctype
                            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. 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

 
 
 

Need to query for duplicates only-please help

Post by Anith Se » Fri, 30 Aug 2002 03:15:10


Since the combination of DocNum, DocType is unique based on
the data you posted you can just try,

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

--
- Anith

 
 
 

Need to query for duplicates only-please help

Post by Princ » Fri, 30 Aug 2002 03:20:55


Minor correcttion.. since you do not need Count(*) Just this would work
fine.. You do not need IN clause:

 SELECT     docnum, doctype
                              FROM          ledocs
                           GROUP BY docnum, doctype
                             HAVING      COUNT(*) > 1


> SELECT     docnum, doctype,count(*)
>                              FROM          ledocs
>                           GROUP BY docnum, doctype
>                             HAVING      COUNT(*) > 1


> > 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

 
 
 

1. Need query to show 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.

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. VB 5.0 - multiple tables and queries.

3. Please help with this "Duplicates" query

4. Random Rows with SP

5. APPROACH QUERY-PLEASE PLEASE PLEASE PLEASE HELP

6. Please Help!!!

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

8. Sybperl

9. APPROACH QUERY-PLEASE PLEASE PLEASE HELP

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

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

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

13. Need help with a SQL/Access query please.