locked
Select rows with 2 or more Repeat id RRS feed

  • Question

  • User-1687766116 posted

    Hi,

    i want select rows with 2 or more Repeat id.

    for example:

    tb1:

    id         id_r

    1          101

    2          101

    2           103

    3           104

    i want this result:

    id         id_r

    2          101

    2           103

    how i do?

    help me please.

    Sunday, June 10, 2018 5:54 AM

All replies

  • User-369506445 posted

    hi

    your mean is , how to show duplicate row ?

    if yes , please try below code

    select count (1) as count, idr  
    from #TempTable t1 
    group by idr

    result

    count   IDR 
    2	100
    2	101
    1	102
    1	105
    Sunday, June 10, 2018 6:50 AM
  • User-369506445 posted

    also if you want show only the  rows that are duplicate , you can try below code :

    select count (1) as count, idr  
    from #TempTable  
    group by idr
    having (count(1)>1)

    note : just replace your table name with #TempTable

    Sunday, June 10, 2018 7:21 AM
  • User347430248 posted

    Hi mehr_83,

    You can try to refer example below.

    Table:

    Query:

    SELECT id,id_r 
    FROM Table_3 WHERE 
    id 
    in (SELECT id 
    FROM Table_3 GROUP BY 
    id HAVING COUNT(*)>1) 

    Output:

    Regards

    Deepak

    Monday, June 11, 2018 12:37 AM
  • User77042963 posted
    CREATE TABLE tb1(
       id   INTEGER  NOT NULL  
      ,id_r INTEGER  NOT NULL
    );
    INSERT INTO tb1(id,id_r) VALUES (1,101),(2,101),(2,103),(3,104);
    
    
    
    ;with mycte as (
    select id,id_r, count(*) Over(Partition by  id ) cnt
     from tb1
    )
    Select id,id_r from mycte
    WHERE cnt>1
    
    drop table tb1

    Wednesday, June 20, 2018 6:08 PM