Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL - Find duplicate records in in two columns

Posted on 2014-09-14
2
Medium Priority
?
1,426 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 66

Accepted Solution

by:
Jim Horn earned 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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