how to find duplicate values using row_number and partition by

9.5k views Asked by At

I would like to know how to get all the duplicate values using the following query.please let me know what i am doing wrong with the query.

regards, Iftekhar

SQL> desc tmp_emp_Area
 Name                            Null?    Type
 ------------------------------- -------- ----
 SC_CD                                    VARCHAR2(2)
 DIST_CD                                  VARCHAR2(2)
 THA_CD                                   VARCHAR2(2)
 UN_CD                                    VARCHAR2(3)
 FP_ID                                    VARCHAR2(4)
 S_DT                                     DATE
 END_DT                                   DATE
 PERFORM                                  VARCHAR2(1)
 BS_CD                                    VARCHAR2(4)
 MKT_CD                                   VARCHAR2(3)

Query :

  SELECT SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,
    row_number() over(partition BY SC_CD, DIST_CD, THA_CD, UN_CD, FP_ID order by FP_ID) rn
    FROM tmp_emp_area
    WHERE rn >1

SQL> / WHERE rn >1 * ERROR at line 4: ORA-00904: "RN": invalid identifier

Please check these two results below that i got after running two different queries to get duplicates.one is showing 92 columns and other is showing 96 columns.

select SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,count(fp_id)  
from tmp_emp_area 
group by SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID 
having count(fp_id)>1
/

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 02 022 5J85            2
14 61 02 098 5J85            3
14 64 02 004 5J85            2
14 64 02 002 5J85            2
14 60 19 060 5F77            2
14 60 13 077 5F77            2
14 61 06 006 5D51            2
14 61 07 013 5D51            2
14 61 07 083 5D51            2
14 61 06 010 5D51            2
14 61 01 015 5R44            2
14 61 08 027 5R44            2
14 61 01 057 5R44            2
14 61 01 067 5R44            2
14 61 05 001 5R44            2
14 61 05 003 5R44            2
14 61 02 009 5J85            2
14 60 13 078 5F77            2
14 61 06 007 5D51            2
14 61 01 021 5R44            2
14 61 01 029 5R44            2

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 01 065 5R44            2
14 61 01 069 5R44            2
14 64 03 013 5J85            2
14 61 02 014 5J85            2
14 61 02 089 5J85            2
14 60 19 132 5F77            2
14 60 19 134 5F77            2
14 61 07 086 5D51            2
14 61 06 035 5D51            2
14 61 06 014 5D51            2
14 61 01 031 5R44            2
14 61 01 036 5R44            2
14 61 01 041 5R44            2
14 61 02 092 5J85            3
14 61 02 074 5J85            3
14 61 02 088 5J85            2
14 61 02 109 5J85            2
14 60 19 014 5F77            2
14 61 07 015 5D51            2
14 61 06 008 5D51            2
14 61 06 016 5D51            2

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 05 047 5R44            2
14 61 01 018 5R44            2
14 61 01 055 5R44            2
14 61 01 066 5R44            2
14 61 01 024 5R44            2
14 61 02 093 5J85            3
14 64 02 011 5J85            2
14 64 02 003 5J85            2
14 61 09 002 5J85            2
14 61 02 081 5J85            2
14 61 05 053 5D51            2
14 61 07 087 5D51            2
14 61 06 036 5D51            2
14 61 06 020 5D51            2
14 61 01 076 5R44            2
14 61 02 059 5R44            2
14 61 02 033 5J85            2
14 64 02 008 5J85            2
14 64 02 020 5J85            2
14 61 02 097 5J85            2
14 61 02 017 5J85            2

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 02 082 5J85            2
14 61 01 077 5R44            2
14 61 05 046 5R44            2
14 61 01 017 5R44            2
14 61 01 054 5R44            2
14 64 02 030 5J85            2
14 61 02 010 5J85            2
14 61 02 103 5J85            2
14 64 02 006 5J85            2
14 64 03 020 5J85            2
14 61 02 105 5J85            2
14 61 02 080 5J85            2
14 61 02 151 5J85            2
14 60 19 059 5F77            2
14 61 06 045 5D51            2
14 61 01 075 5R44            2
14 61 01 056 5R44            2
14 61 01 020 5R44            2
14 61 05 007 5R44            2
14 61 01 053 5R44            2
14 61 01 078 5R44            2

SC DI TH UN_ FP_I COUNT(FP_ID)
-- -- -- --- ---- ------------
14 61 02 013 5J85            2
14 64 02 010 5J85            2
14 64 02 001 5J85            2
14 61 02 077 5J85            2
14 61 07 033 5D51            2
14 61 01 033 5R44            2
14 61 01 068 5R44            2
14 61 01 073 5R44            2

92 rows selected.



  select *
 from
  (
    SELECT SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,
       row_number() over(partition BY SC_CD, DIST_CD, THA_CD, UN_CD, FP_ID order by FP_ID) rn
    FROM tmp_emp_area
  ) dt
 WHERE rn >1
SQL> /


SC DI TH UN_ FP_I        RN
-- -- -- --- ---- ---------
14 60 13 077 5F77         2
14 60 13 078 5F77         2
14 60 19 014 5F77         2
14 60 19 059 5F77         2
14 60 19 060 5F77         2
14 60 19 132 5F77         2
14 60 19 134 5F77         2
14 61 01 015 5R44         2
14 61 01 017 5R44         2
14 61 01 018 5R44         2
14 61 01 020 5R44         2
14 61 01 021 5R44         2
14 61 01 024 5R44         2
14 61 01 029 5R44         2
14 61 01 031 5R44         2
14 61 01 033 5R44         2
14 61 01 036 5R44         2
14 61 01 041 5R44         2
14 61 01 053 5R44         2
14 61 01 054 5R44         2
14 61 01 055 5R44         2

SC DI TH UN_ FP_I        RN
-- -- -- --- ---- ---------
14 61 01 056 5R44         2
14 61 01 057 5R44         2
14 61 01 065 5R44         2
14 61 01 066 5R44         2
14 61 01 067 5R44         2
14 61 01 068 5R44         2
14 61 01 069 5R44         2
14 61 01 073 5R44         2
14 61 01 075 5R44         2
14 61 01 076 5R44         2
14 61 01 077 5R44         2
14 61 01 078 5R44         2
14 61 02 009 5J85         2
14 61 02 010 5J85         2
14 61 02 013 5J85         2
14 61 02 014 5J85         2
14 61 02 017 5J85         2
14 61 02 022 5J85         2
14 61 02 033 5J85         2
14 61 02 059 5R44         2
14 61 02 074 5J85         2

SC DI TH UN_ FP_I        RN
-- -- -- --- ---- ---------
14 61 02 074 5J85         3
14 61 02 077 5J85         2
14 61 02 080 5J85         2
14 61 02 081 5J85         2
14 61 02 082 5J85         2
14 61 02 088 5J85         2
14 61 02 089 5J85         2
14 61 02 092 5J85         2
14 61 02 092 5J85         3
14 61 02 093 5J85         2
14 61 02 093 5J85         3
14 61 02 097 5J85         2
14 61 02 098 5J85         2
14 61 02 098 5J85         3
14 61 02 103 5J85         2
14 61 02 105 5J85         2
14 61 02 109 5J85         2
14 61 02 151 5J85         2
14 61 05 001 5R44         2
14 61 05 003 5R44         2
14 61 05 007 5R44         2

SC DI TH UN_ FP_I        RN
-- -- -- --- ---- ---------
14 61 05 046 5R44         2
14 61 05 047 5R44         2
14 61 05 053 5D51         2
14 61 06 006 5D51         2
14 61 06 007 5D51         2
14 61 06 008 5D51         2
14 61 06 010 5D51         2
14 61 06 014 5D51         2
14 61 06 016 5D51         2
14 61 06 020 5D51         2
14 61 06 035 5D51         2
14 61 06 036 5D51         2
14 61 06 045 5D51         2
14 61 07 013 5D51         2
14 61 07 015 5D51         2
14 61 07 033 5D51         2
14 61 07 083 5D51         2
14 61 07 086 5D51         2
14 61 07 087 5D51         2
14 61 08 027 5R44         2
14 61 09 002 5J85         2

SC DI TH UN_ FP_I        RN
-- -- -- --- ---- ---------
14 64 02 001 5J85         2
14 64 02 002 5J85         2
14 64 02 003 5J85         2
14 64 02 004 5J85         2
14 64 02 006 5J85         2
14 64 02 008 5J85         2
14 64 02 010 5J85         2
14 64 02 011 5J85         2
14 64 02 020 5J85         2
14 64 02 030 5J85         2
14 64 03 013 5J85         2
14 64 03 020 5J85         2

96 rows selected.
1

There are 1 answers

1
dnoeth On

You can't use an alias in WHERE, switch to a Derived Table:

select *
from
 (
   SELECT SC_CD,DIST_CD,THA_CD,UN_CD,FP_ID,
      row_number() over(partition BY SC_CD, DIST_CD, THA_CD, UN_CD, FP_ID order by FP_ID) rn
   FROM tmp_emp_area
 ) dt
WHERE rn >1