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,665 questions
{count} votes

Accepted answer
  1. Tom Cooper 8,471 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,206 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 26,201 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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.