1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

To eliminate duplicates

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Dec 22, 2016.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi,
    I have a output as below:

    col1 col2 col3 col4
    1a 2a 3a null
    1a 2a 3a TAM
    1b 2b 3b TAM
    1b 2b 3b null
    1c 2c 3c null

    As you see there are duplicates in the first 3 columns col1, col2, col3. However, col4 has null or TAM.

    My requirement is to get unique records like below.

    col1 col2 col3 col4
    1a 2a 3a TAM
    1b 2b 3b TAM
    1c 2c 3c null

    Kindly help
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    774
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    simple example :
    Code (SQL):
    SELECT * FROM (SELECT
    col1,col2,col3 ,col4,
    ROW_NUMBER()  OVER (partition BY col1,col2,col3 ORDER BY col4 NULLS LAST ) rn
    FROM your_table)
    WHERE  rn = 1
     
    Last edited: Dec 22, 2016
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,619
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You currently have unique records as col4 makes each one unique. Apparently you want col1, col2, col3 to be unique, which can be accomplished by using a UNIQUE INDEX on col1, col2, col3.