Find Duplicates Records and Assign 'Y' and 'N' to Them in SQL Developer-CodePudding

I want to Assign 'Y' to the Duplicate Records & 'N' to the Uinque Records, And Display those 'Y' & 'N' Flags in Another Column Called 'Duplicate', which is Actually Not Present in the Table. Like Below Image

Source Table:
-------------
Name,Location
Ram,India
Ram,USA
Ram,Japan
Ram,India
Ram,UK

Target Table:(Output I want)
----------------------------
Name,Location,Duplicate
Ram,India,Y
Ram,India,Y
Ram,Japan,N
Ram,UK,N
Ram,USA,N

Name,Location,Duplicate
Ram,India,N
Ram,India,Y
Ram,Japan,N
Ram,UK,N
Ram,USA,N

How to Write a Query in Oracle/SQL Developer.

CodePudding user response：

One option is to conditionally use count analytic function.

Sample data:

SQL> with test (name, location) as
  2    (select 'Ram', 'India' from dual union all
  3     select 'Ram', 'USA'   from dual union all
  4     select 'Ram', 'Japan' from dual union all
  5     select 'Ram', 'India' from dual union all
  6     select 'Ram', 'UK'    from dual
  7    )

Query:

  8  select
  9    name,
 10    location,
 11    case when count(*) over (partition by name, location) > 1 then 'Y'
 12         else 'N'
 13    end duplicate
 14  from test;

NAM LOCAT D
--- ----- -
Ram India Y
Ram India Y
Ram Japan N
Ram UK    N
Ram USA   N

SQL>

CodePudding user response：

You can use ROW_NUMBER() OVER() analytic function.
Assuming that the first one of multiplyed records is considered as unique and the second and every possible other is considered as duplicate here is the code:

WITH    -- Sample data
    tbl (A_NAME, LOC) as
        (
            Select 'Ram', 'India' From Dual Union All
            Select 'Ram', 'USA'   From Dual Union All
            Select 'Ram', 'Japan' From Dual Union All
            Select 'Ram', 'India' From Dual Union All
            Select 'Ram', 'UK'    From Dual
        )

-- S Q L
Select  A_NAME, LOC, 
        Case When ROW_NUMBER() OVER(Partition By A_NAME, LOC Order By A_NAME, LOC) = 1 THEN 'N' ELSE 'Y' END "DUPLICATE"
From    tbl

R e s u l t:
A_NAME LOC   DUPLICATE
------ ----- ---------
Ram    India N         
Ram    India Y         
Ram    Japan N         
Ram    UK    N         
Ram    USA   N