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

Question

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

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

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

OR

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.

Answer 1

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       

OR

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

A_NAME LOC   DUPLICATE
------ ----- ---------
Ram    India Y         
Ram    India Y         
Ram    Japan N         
Ram    UK    N         
Ram    USA   N