This discussion is archived
10 Replies Latest reply: Nov 20, 2012 9:49 AM by user13179060 RSS

how can remove duplicates

sunmat45 Newbie
Currently Being Moderated
Alcon,

Can you please give me good suggestion how to show any one record from the duplicate records.

im getting many duplicates but i want show any one location with lot number, if im using ROWNUM=1 will display totally one record. can you give me any idea is it possble to show any one.

we have three location only like ABC,BCD,JKL but we have N no of lot numbers....

example. im getting output is

Lot no Location
123 ABC
123 BCD
145 ABC
145 JKL
178 JKL
178 ABC
190 BCD
190 ABC


now i want show below output
Lot no Location
123 ABC
145 ABC
178 JKL
190 BCD
  • 1. Re: how can remove duplicates
    jeneesh Guru
    Currently Being Moderated
    are ou OK, with any location for a lot_no?
    select lot_no,min(location) loactaion
    from your_table
    group by lot_no
  • 2. Re: how can remove duplicates
    sunmat45 Newbie
    Currently Being Moderated
    Hi Jeneesh,

    thanks for reply...

    But still im getting same result with duplicates.... im using below query...

    SELECT DISTINCT LX.LOT,
    MIN(L.LOCPREFIX) AS LOC

    FROM LOTXLOCXID LX,
    LOC L,
    SKU S

    WHERE L.LOC=LX.LOC
    AND S.SKU=LX.SKU
    AND S.PVTYPE='GFM'

    GROUP BY LX.LOT,L.LOCPREFIX

    im getting duplicates.... one lot number with two different location...
  • 3. Re: how can remove duplicates
    jeneesh Guru
    Currently Being Moderated
    try this
    SELECT LX.LOT,MIN(L.LOCPREFIX) AS LOC 
    FROM      LOTXLOCXID LX,
         LOC L,
         SKU S 
    WHERE L.LOC=LX.LOC 
    AND S.SKU=LX.SKU 
    AND S.PVTYPE='GFM' 
    GROUP BY LX.LOT
    The column, which is used in the SUMMARY function should not be used in the GROUP BY for your query.

    And when you are grouping, you dont need to use DISTINCT

    Edited by: jeneesh on Nov 20, 2012 10:31 AM
  • 4. Re: how can remove duplicates
    930854 Newbie
    Currently Being Moderated
    SELECT DISTINCT(lot_no),
    CASE
    WHEN lot_no = '123'
    THEN MIN(location)
    WHEN lot_no= '145'
    THEN MIN(location)
    WHEN lot_no = '178'
    THEN MAX(location)
    WHEN lot_no = '190'
    THEN MAX(location)
    END
    FROM your_table_name
    GROUP BY lot_no


    use this i think yo will get an answer.
  • 5. Re: how can remove duplicates
    jeneesh Guru
    Currently Being Moderated
    927851 wrote:
    SELECT DISTINCT(lot_no),
    CASE
    WHEN lot_no = '123'
    THEN MIN(location)
    WHEN lot_no= '145'
    THEN MIN(location)
    WHEN lot_no = '178'
    THEN MAX(location)
    WHEN lot_no = '190'
    THEN MAX(location)
    END
    FROM your_table_name
    GROUP BY lot_no
    What if, one more lot_no is added?
  • 6. Re: how can remove duplicates
    930854 Newbie
    Currently Being Moderated
    sunmat45 raised the question for like tat

    example. im getting output is

    Lot no Location
    123 ABC
    123 BCD
    145 ABC
    145 JKL
    178 JKL
    178 ABC
    190 BCD
    190 ABC

    now i want show below output
    Lot no Location
    123 ABC
    145 ABC
    178 JKL
    190 BCD


    wat is the user for tat no?
  • 7. Re: how can remove duplicates
    jeneesh Guru
    Currently Being Moderated
    927851 wrote:
    sunmat45 raised the question for like tat

    example. im getting output is

    Lot no Location
    123 ABC
    123 BCD
    145 ABC
    145 JKL
    178 JKL
    178 ABC
    190 BCD
    190 ABC

    now i want show below output
    Lot no Location
    123 ABC
    145 ABC
    178 JKL
    190 BCD


    wat is the user for tat no?
    the given data is just sample ..
    Original data will be different..So the query should be always generic.. :)
  • 8. Re: how can remove duplicates
    971895 Journeyer
    Currently Being Moderated
    Try like...

    SELECT * FROM (SELECT LOT,LOCATION,row_number() over( partition by LOT ORDER BY LOCATION) rn FROM TEST) where rn=1
  • 9. Re: how can remove duplicates
    Manik Expert
    Currently Being Moderated
    WITH t AS
            (SELECT 123 Lot_no, 'ABC' Location FROM DUAL
             UNION ALL
             SELECT 123, 'BCD' Location FROM DUAL
             UNION ALL
             SELECT 145, 'ABC' Location FROM DUAL
             UNION ALL
             SELECT 145, 'JKL' Location FROM DUAL
             UNION ALL
             SELECT 178, 'JKL' Location FROM DUAL
             UNION ALL
             SELECT 178, 'ABC' Location FROM DUAL
             UNION ALL
             SELECT 190, 'BCD' Location FROM DUAL
             UNION ALL
             SELECT 190, 'ABC' Location FROM DUAL),
         t1 AS
            (  SELECT a.*, ROW_NUMBER () OVER (PARTITION BY lot_no ORDER BY 1) rn
                 FROM t a
             ORDER BY lot_no)
    SELECT lot_no, location
      FROM t1
     WHERE rn = 1;
    Cheers,
    Manik.
  • 10. Re: how can remove duplicates
    user13179060 Newbie
    Currently Being Moderated
    select * from t1
    where rowid in (select MIN(rowid) from t1
    group by id )

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points