10 Replies Latest reply: Nov 20, 2012 11:49 AM by user13179060 RSS

    how can remove duplicates

    sunmat45
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            select * from t1
                            where rowid in (select MIN(rowid) from t1
                            group by id )