How to find duplicate rows. - SQL Server Forums

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

Transact-SQL (2005)

How to find duplicate rows.

Author

Topic

anishap
Yak Posting Veteran

61 Posts

Posted - 2009-03-12 : 14:07:07

Hi,

I have an employee table and vaccine table. I would like to find out the duplicate vaccine records with same date and type from the below table.

Vaccine table:

Date: 01/01/2001
Vaccine Type: Influenza

Date: 03/20/1998
Vaccine Type: Hepatitis B

Date:01/01/2001
Vaccine Type: Influenza

Date:03/19/2002
Vaccine Type: Influenza

The query should find out the above duplicate Influenza record with same date (01/01/2001)

I tried the below query but it didn't work

SELECT E.ID,E.LNAME,E.FNAME,I.DATE,I.TYPE FROM IMMUNE I,EMPLOYEE E
WHERE E.REC_NUM = I.EMPLOYEE
GROUP BY E.ID,E.LNAME,E.FNAME,I.DATE,I.TYPE
HAVING COUNT(I.TYPE) > 1 AND COUNT(I.DATE)> 1

Can anyone help me with this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 14:12:40

[code]SELECT Date,[Vaccine Type]
FROM
(
SELECT ROW_NUMBER () OVER (PARTITION BY Date,[Vaccine Type] ORDER BT [Date]) AS Seq,Date,[Vaccine Type]
FROM Table
)t
WHERE Seq>1
[/code]

anishap
Yak Posting Veteran

61 Posts

Posted - 2009-03-12 : 14:24:08

I need to join employee and vaccine table.

anishap
Yak Posting Veteran

61 Posts

Posted - 2009-03-12 : 14:34:34

When I tried the above I'm getting incorrect syntax error

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-12 : 14:48:17

The query looks fine. Whats the error you get ? Also, can you post the exact query you tried?

anishap
Yak Posting Veteran

61 Posts

Posted - 2009-03-12 : 14:59:12

I'm not getting any error. Now the below query is working fine.

SELECT E.ID,E.LNAME,E.FNAME,I.DATE,I.TYPE FROM IMMUNE I,EMPLOYEE E
WHERE E.REC_NUM = I.EMPLOYEE
GROUP BY E.ID,E.LNAME,E.FNAME,I.DATE,I.TYPE
HAVING COUNT(I.TYPE) > 1 AND COUNT(I.DATE)> 1

Some how I didn't get any result when I tried before. Not sure what was the problem.

Thanks for checking this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources