Solved

SQL - Find duplicate records in in two columns

Posted on 2014-09-14
2
610 Views
Last Modified: 2014-09-14
Hi Experts,
I would like to find multiple records, where the value in the Emp_Id column and the WeekRequested column is the same.
(I'd like to know if the same employee requested the same week more than once)
The query below doesn't return the expected result. How can it be modified to achieve my goal? Thanks for your help.
SELECT Emp_Id, WeekRequested
FROM Auto_Table
GROUP BY Emp_Id, WeekRequested
HAVING COUNT(*) > 1
ORDER BY Emp_Id

Open in new window

0
Comment
Question by:romsom
2 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40321864
Give this a whirl...
SELECT Emp_Id, WeekRequested, COUNT(WeekRequested) as the_count
FROM Auto_Table
GROUP BY Emp_Id, WeekRequested
HAVING COUNT(WeekRequested) > 1
ORDER BY Emp_Id, WeekRequested

Open in new window

btw I just kicked out an article titled SQL Server Delete Duplicate Rows Solutions that would be a good read for the many ways to hide-show-delete duplicate rows.   If you like it please it the 'Yes' button next to 'Was this article helpful'?
0
 

Author Closing Comment

by:romsom
ID: 40321885
Thank you, this is perfect. The article is also very useful. I managed to delete all duplicates with your method.
;with a as (
   SELECT Emp_Id, WeekRequested, 
      row_number() OVER (partition by Emp_Id, WeekRequested ORDER BY WeekRequested) as row_number 
   FROM Auto_Table 
)
DELETE FROM a WHERE row_number > 1

Open in new window

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now