Select all duplicate records

Jen 61 Reputation points
2020-10-14T15:51:30.563+00:00

How do I return all records that have the same last 6 digits or characters?
For example:
000123456
009123456
123456789
100456789
201456789
987654321
112233445

I need to return:
000123456
009123456
123456789
100456789
201456789

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions
{count} votes

Accepted answer
  1. Tom Cooper 8,461 Reputation points
    2020-10-14T16:16:26.027+00:00
    ;With cte As 
    (Select YourColumn, 
      Count(*) Over(Partition By Right(YourColumn, 6)) As cnt
    From YourTable)
    Select YourColumn
    From cte
    Where cnt > 1;
    

    Replace YourColumn and YourTable with the your column name and table name.
    Tom

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Jen 61 Reputation points
    2020-10-14T16:23:50.2+00:00

    Not working...
    it only returns:
    000123456
    201456789


  2. Guoxiong 8,126 Reputation points
    2020-10-14T16:42:39.893+00:00
    DECLARE @T TABLE (
        Col varchar(20)
    );
    INSERT INTO @T VALUES
    ('000123456'),
    ('009123456'),
    ('123456789'),
    ('100456789'),
    ('201456789'),
    ('987654321'),
    ('112233445');
    
    SELECT t.Col
    FROM @T AS t
    INNER JOIN (
        SELECT RIGHT(Col, 6) AS [6_Digits]
        FROM @T
        GROUP BY RIGHT(Col, 6)
        HAVING COUNT(*) > 1
    ) AS g ON g.[6_Digits] = RIGHT(t.Col, 6);
    
    0 comments No comments

  3. Yitzhak Khabinsky 24,561 Reputation points
    2020-10-14T16:45:39.497+00:00

    Hi @Jen ,

    Please find below a minimal reproducible example.
    It is a copycat from the Tom Cooper solution.
    All credit goes to Tom.

    SQL

    -- DDL and sample data population, start  
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col char(9));  
    INSERT INTO @tbl VALUES  
    ('000123456'),  
    ('009123456'),  
    ('123456789'),  
    ('100456789'),  
    ('201456789'),  
    ('987654321'),  
    ('112233445');  
    -- DDL and sample data population, end  
      
    ;WITH rs AS   
    (  
      SELECT id, col,   
        COUNT(*) OVER(PARTITION BY RIGHT(col, 6)) AS cnt  
      FROM @tbl  
     )  
     SELECT id, col, cnt  
     FROM rs  
     WHERE cnt > 1  
     ORDER BY Right(col, 6);  
    

    Output

    +----+-----------+-----+  
    | id |    col    | cnt |  
    +----+-----------+-----+  
    |  1 | 000123456 |   2 |  
    |  2 | 009123456 |   2 |  
    |  3 | 123456789 |   3 |  
    |  4 | 100456789 |   3 |  
    |  5 | 201456789 |   3 |  
    +----+-----------+-----+  
    
    0 comments No comments

  4. Jen 61 Reputation points
    2020-10-14T18:12:37.457+00:00

    Hi YitzhakKhabinsky-0887,

    it worked great, thanks
    I have given credit to Tom Cooper per your comments, tried to give to both of you but it wouldn't le me.