none
Compare rows with duplicate values

    Question

  • I want to find duplicate rows in a table. I can find a count of duplicates as listed below but I want to include all of the rows in the result so that I can compare values in other columns to find out which I should delete. I want the result to show the Count column. This query seems to only show the row once with a count.  How would I do that? I want to use the most ansi standard sql that will work.

    SELECT UserID,ScheduleDaysID, count(*) as Count FROM [dbo].[LMS_Attendance] lms
    GROUP BY lms.UserID,lms.ScheduleDaysID
    HAVING count(*)> 1
    order by UserID

    UserID ScheduleDaysID Count
    D4E36823-1111-40E4-1111-01EB10F3776A B5FA3518-1111-4932-1111-49325F3A383F 2


    • Edited by Tom ERP Wednesday, May 15, 2019 9:16 PM add
    Wednesday, May 15, 2019 7:17 PM

All replies

  • Hi Tom ERP,

    CTE and ranking functions could help for your scenario.

    Please see below:

    DECLARE @tbl TABLE (ID INT NOT NULL
                , State VARCHAR(30) NOT NULL
                , City VARCHAR(30) NOT NULL
                , Population INT NOT NULL);
    
    INSERT INTO @tbl(ID, State, City, Population)
    VALUES (1, 'FL','Miami', 100)
       , (2, 'FL','Orlando', 200)
       , (1, 'FL','Miami', 770)
       , (3, 'TX','Dallas', 540);
    
    
    WITH rs AS 
    (
       SELECT *, 
          ROW_NUMBER() OVER(PARTITION BY ID, State, City ORDER BY Population) AS rn
       FROM @tbl
    )
    SELECT * FROM rs;
    --WHERE rn = 1;

    Wednesday, May 15, 2019 7:31 PM
  • You still can use the GROUP BY statement in the subquery:

    SELECT lms.UserID,lms.ScheduleDaysID
    FROM [dbo].[LMS_Attendance] AS lms
    WHERE EXISTS
    (
    	SELECT UserID, ScheduleDaysID
    	FROM [dbo].[LMS_Attendance]
    	WHERE UserID = lms.UserID AND ScheduleDaysID = lms.ScheduleDaysID
    	GROUP BY UserID, ScheduleDaysID
    	HAVING COUNT(*) > 1
    )


    A Fan of SSIS, SSRS and SSAS

    Wednesday, May 15, 2019 7:43 PM
  • Hi Tom ERP,

     

    Per your description , please try to use  window functions.

     

    ;with cte as (
    SELECT *,count(*)over (partition by lms.UserID,lms.ScheduleDaysID) as Count 
    FROM [dbo].[LMS_Attendance] lms
    )
    select * from cte where Count>1

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 16, 2019 2:55 AM