Need query to show duplicates only-please help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need query to show duplicates only-please help

Author

Topic

utleya
Starting Member

3 Posts

Posted - 2002-08-28 : 14:26:55

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

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-08-28 : 14:31:00

the in is invalid in sql server i think ... try this instead

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources