Solved

SQL - Find duplicate records in in two columns

Posted on 2014-09-14
2
829 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help how to find where my error is in UFD 6 39
SQL Select in Access 2003 3 20
query optimization 6 13
Accessing variables in MySQL query 4 19
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

790 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