0

I have a table that has the data as below where the combination of LINENUM and RCPTNUM should be unique for each PONUM and SITE. 

PONUM LINENUM RCPTNUM SITE
1000     1      1      XXX
1000     1      2      XXX
1000     1      3      XXX
1000     2      1      XXX
1000     2      1      XXX
1001     1      1      XXX
1001     1      1      XXX

The output should show the list of duplicate combination

PONUM LINENUM RCPTNUM SITE
1000   2        1     XXXX
1001   1        1     XXXX
max092012
  • 365
  • 1
  • 5
  • 25
  • 2
    You should add what you are asking for. Looking for how to normalize the given table? Get help in building a query identifying "duplicates"? – rpy Dec 28 '16 at 07:17
  • What's your question? – additionster Dec 28 '16 at 07:23
  • Possible duplicate of [How do I find duplicate values in a table in Oracle?](http://stackoverflow.com/questions/59232/how-do-i-find-duplicate-values-in-a-table-in-oracle) – Aleksej Dec 28 '16 at 09:57

2 Answers2

0

Try this:

select PONUM, 
       LINENUM, 
       RCPTNUM ,
       SITE 
from 
( --Preparing dataset
 with tbl( PONUM, LINENUM, RCPTNUM ,SITE) as 
(select 1000,     1 ,     1 ,     'XXX' from dual
 UNION ALL
 select 1000,     1,      2,      'XXX' from dual
 UNION ALL
 select 1000,     1,      3,      'XXX' from dual
 UNION ALL
 select 1000,     2,      1,      'XXX' from dual
 UNION ALL
 SELECT 1000,     2,      1,      'XXX' from dual
 UNION ALL
 Select 1001,     1,      1,      'XXX' from dual
 UNION ALL
 Select 1001,     1,      1,      'XXX' from dual )
 ---Dataset end
 ---Query
select PONUM,
       LINENUM,
       RCPTNUM,SITE ,
       row_number()over(partition by PONUM, LINENUM,RCPTNUM,SITE order by PONUM ) rnk
from tbl
)
where rnk = 2 ;
XING
  • 9,608
  • 4
  • 22
  • 38
0
select PONUM, LINENUM, RCPTNUM, SITE
from yourTable
group by PONUM, LINENUM, RCPTNUM, SITE
having count(1) > 1
Aleksej
  • 22,443
  • 5
  • 33
  • 38