CODEWITHSUNDEEP
sqloracle-database
user1683987
user1683987

Reputation: 533

Table has unique rows, however how can I find duplicates of certain columns?

I have a Table such that looks like the following: 

ID         TID        TNAME     CID     CNAME     SEQUENCE
-----------------------------------------------------------
200649     6125     Schalke    356954   Mirko         1
200749     6125     Schalke    356954   Mirko         1
200849     6125     Schalke    439386   Fred          1
200849     6125     Schalke    356954   Mirko         1
200849     6125     Schalke    495881   Michael       1
200949     6125     Schalke    401312   Felix         1
200949     6125     Schalke    495881   Michael       2

I would like to query this table so it only returns if ID and SEQUENCE are duplicated. i.e. it should only return: 

200849     6125     Schalke    439386   Fred          1
200849     6125     Schalke    356954   Mirko         1
200849     6125     Schalke    495881   Michael       1

I have used having count(ID) > 1 but it will not return anything since CIDs are all unique.

Thanks for your help!

Upvotes: 1

Views: 102

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I like using analytic functions for these things:

select t.*
from (select t.*, count(*) over (partition by id, sequence) as cnt
      from t
     ) t
where cnt > 1

This also gives you the number of duplicates on each row in the output.

Upvotes: 2

Barranka
Barranka

Reputation: 21047

I think this is a way to do it:

select a.*
from yourTable as a
inner join (
    select id, sequence 
    from yourTable 
    group by id, sequence 
    having count(id)>1) as b on a.id = b.id and a.sequence=b.sequence

Upvotes: 3

Kermit
Kermit

Reputation: 34054

Something like this?

SELECT b.id, 
       b.tid, 
       b.tname, 
       b.cid, 
       b.cname, 
       b.sequence 
FROM   (SELECT id, 
               sequence, 
               Count(*) CNT 
        FROM   table1 
        GROUP  BY id, 
                  sequence 
        HAVING Count(*) > 1) a 
       LEFT JOIN table1 b 
              ON b.id = a.id 
                 AND b.sequence = a.sequence

Result

|     ID |  TID |   TNAME |    CID |   CNAME | SEQUENCE |
---------------------------------------------------------
| 200849 | 6125 | Schalke | 439386 |    Fred |        1 |
| 200849 | 6125 | Schalke | 356954 |   Mirko |        1 |
| 200849 | 6125 | Schalke | 495881 | Michael |        1 |

See the demo

Upvotes: 2

Related Questions