none
Select duplicate values in tables RRS feed

  • Question

  • Hello,

    I have MS SQL 2012 Std. I have little question - how select (view) duplicate values in one or several tables?

    Thanks!

    Monday, May 30, 2016 1:01 PM

Answers

All replies

  • You can use GROUP BY in the following fashion:

    SELECT column1, COUNT(column1) AS NumOfOccurrences
    FROM table1
    GROUP BY column1
    HAVING ( COUNT(column1) > 1 )


    Thanks,
    Sam Lester (MSFT)


    http://blogs.msdn.com/samlester

    Twitter - @SQLSamLester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.



    Monday, May 30, 2016 1:15 PM
    Moderator
  • You can use GROUP BY in the following fashion:

    SELECT column1, COUNT(column1) AS NumOfOccurrences
    FROM table1
    GROUP BY column1
    HAVING ( COUNT(column1) > 1 )


    Thanks,
    Sam Lester (MSFT)


    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.



    Thanks! But if I need select ALL columns in table and check for duplicate - what script for this?
    Monday, May 30, 2016 2:11 PM
  • select * from
    (
    select *, count(*) over (partition by column1) cnt 
    from table1
    ) t
    where cnt > 1


    http://www.t-sql.ru

    Monday, May 30, 2016 2:45 PM
  • (SELECT * FROM Foobar)
    EXCEPT
    (SELECT DISTINCT * FROM Foobar)

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, May 30, 2016 3:43 PM
  • (SELECT * FROM Foobar)
    EXCEPT
    (SELECT DISTINCT * FROM Foobar)

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    This will not work.  It will always return 0 rows.  See https://msdn.microsoft.com/en-us/library/ms188055.aspx?f=255&MSPPError=-2147217396 where it says "EXCEPT returns distinct rows from the left input query that aren’t output by the right input query."  Or run

    create table #Foo(j int);
    insert #Foo select 1 union all select 1 union all select 1 union all select 2 union all select 2 union all select 3;
    select * from #foo;
    (select * from #Foo)
    except
    (select distinct * from #Foo);
    
    go
    drop table #foo;
    

    The EXCEPT query returns 0 rows even though there are rows with duplicate values.

    Tom

    Monday, May 30, 2016 6:26 PM
  • ARGGH!  That was supposed to be "except all" but that still does not work because Microsoft does not support the full ANSI multi-set operations. That is what I get for bouncing from one SQL forum to another. My bad. 

    I am looking for some reviewers for the next book in my series. It will be on bad SQL and why people write it. Would you be interested in volunteering as a reviewer/reader? If so, give me an email they can use to send to my publisher (MKP/Elsevier). This will be a few months from now, so there is no rush. You get fame, honor, and free books.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, May 30, 2016 7:50 PM
  • Sounds like fun.  tomcooper   #at#  comcast  ?dot?  net

    Tom

    Monday, May 30, 2016 8:16 PM
  • AUTOID is a identity column

    SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
        FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

    • Proposed as answer by Babsu Tuesday, May 31, 2016 11:08 AM
    Tuesday, May 31, 2016 5:17 AM