JavaEar 专注于收集分享传播有价值的技术资料

Oracle Sql; Find same combination of columnvalues in two or more rows

I need some help with my query. Been searching for ages but can`t come up with the right sql statement.

This my table DRAFT DFT (only 1 table, these are all the columns)

RowID|SID Number|Column C|RELS Number|Column E|Dr Number |Column G |
1------ |  23101-----    |21-8-2014| 22234 -----  | UNR---------| 14243-----|2
2------ |  23101 -----   |22-8-2014| 22234 -----  | UNS---------| 14243 ---| 2
3------ |  23101------    |28-8-2014| 22232 -----  | FRE ---------| 14243 ---| 2

What I need is the following :

I need to select all the rows & Columns values of the table where the combination of the value in column SID Number and RELS Number are duplicate so what I must see is ony the following 2 rows:

1------ | 23101----- |21-8-2014| 22234 ----- | UNR---------| 14243-----|2
2------ | 23101 ----- |22-8-2014| 22234 ----- | UNS---------| 14243 ---| 2

AND NOT THIS:

1------ | 23101----- |21-8-2014| 22234 ----- | UNR---------| 14243-----|2
2------ | 23101 ----- |22-8-2014| 22234 ----- | UNS---------| 14243 ---| 2
**3------ | 23101------ |28-8-2014| 22232 ----- | FRE ---------| 14243 ---| 2**

because the combination of SID Number and RELS Number is not duplicate.

I know that the column value of Dr Number is the same in all 3 rows.

Does that matter for my sql statement?

This was my statement:

SELECT *
FROM DRAFT DFT 
INNER JOIN (SELECT SID Number,RELS Number, COUNT(*) AS "TOTALCOUNT
            FROM DRAFT DTF1GROUP BY SID Number,RELS Number
            HAVING COUNT (*)>1
           ) B ON DTF.SID Number=B.SID Number AND DTF.RELS Number=B.RELS Number

1个回答

    最佳答案
  1. Just use analytic functions:

    select d.*
    from (select d.*, count(*) over (partition by sid, rels) as cnt
          from draft
         ) d
    where cnt > 1;