Solved

SQL - Find duplicate records in in two columns

Posted on 2014-09-14
2
996 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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