9 Replies Latest reply: Jul 7, 2014 7:31 AM by Roger RSS

    query latest record in table with duplicate

    Tshifhiwa

      hi i have values in my table which seems duplicate but they are now,the use case is the use pass value from application and based on that value constract the query to return values the use can see,e.g i have table card

       

      CREATE TABLE CARD

         ( CRDIND NUMBER(2,0) primary key,

          CRDPOS1  VARCHAR2(2 BYTE),

          CRDPOS2  VARCHAR2(2 BYTE),

          activedate DATE,

          inactivedate DATE,

          CARDNAME VARCHAR2(50 BYTE)

         )

       

      insert into card values(1,null,null,TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),null,'VISA');

      insert into card values(2,null,null,TO_DATE('2003/06/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),TO_DATE('2003/07/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),'VISA');

      insert into card values(3,null,null,TO_DATE('2003/08/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),null,'VISA');

      insert into card values(4,null,null,TO_DATE('2003/06/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),TO_DATE('2003/07/10 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),'VISA');

      insert into card values(3,null,null,TO_DATE('2003/09/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'),null,'VISA');

       

      the use enter value VISA to query the latest record

        • 1. Re: query latest record in table with duplicate
          David Berger

          Hello Tshifhiwa

           

          Your insert statements can not be executed because of your primary key constraint...

          Error at line 5

          ORA-00001: unique constraint (BDA.SYS_C005408) violated

           

          On which columns should be checked the duplicateness and which column is for determining the latest record?

           

          Thanks and regards, David

          • 2. Re: query latest record in table with duplicate
            Tshifhiwa

            ok sorry for my insert statement column inactivedate will determine the latest record ,because the stiuation the use de-activate is this column inactivedate but if the same card is active again the use create new record with same name but this time  inactivedate column will be null to show that is active,but we can have the latest record with inactivedate with date

            • 3. Re: query latest record in table with duplicate
              David Berger

              The column "inactivedate" can not determine the order because of his nullability... Could you please give us a more detailed and accurate description what you want?

              • 4. Re: query latest record in table with duplicate
                michaelrozar17

                Below query gets the latest record with card-name "VISA" based on the column "activedate"

                 

                select *

                    from

                       (

                        select c.*

                             ,row_number() over(/*partition by X*/ order by activedate desc) rn

                          from card c

                          where cardname='VISA'

                       )

                    where rn=1

                • 5. Re: query latest record in table with duplicate
                  Tshifhiwa

                  i what to query the latest activedate or inactive date of record with value visa

                   

                  will this be corect

                  select activedate

                      from

                         (

                          select c.*

                               ,row_number() over(/*partition by X*/ order by activedate desc) rn

                            from card c

                            where cardname='VISA'

                         )

                      where rn=1

                   

                   

                  select inactivedate

                      from

                         (

                          select c.*

                               ,row_number() over(/*partition by X*/ order by activedate desc) rn

                            from card c

                            where cardname='VISA'

                         )

                      where rn=1

                   

                  • 6. Re: query latest record in table with duplicate
                    David Berger

                    I hope it helps:

                    SELECT crdind, crdpos1, crdpos2, activedate, inactivedate, cardname

                      FROM (SELECT crdind, crdpos1, crdpos2, activedate, inactivedate, cardname

                                 , ROW_NUMBER() OVER (ORDER BY inactivedate DESC, activedate DESC) AS ORDER_NUM

                              FROM CARD

                           )

                    WHERE order_num = 1;

                     

                    Regards, David

                    • 7. Re: query latest record in table with duplicate
                      Roger

                      i would add:

                       

                      ... ROW_NUMBER() OVER (/* partition by X*/ ORDER BY activedate DESC NULLS LAST)

                       

                      or i would remove the rows with a null value in activedate in the WHERE clause...

                       

                      hth

                      • 8. Re: query latest record in table with duplicate
                        Tshifhiwa

                        hi if i did this

                        SELECT  inactivedate

                              FROM (SELECT inactivedate

                                         , ROW_NUMBER() OVER (ORDER BY inactivedate DESC, activedate DESC) AS ORDER_NUM

                                      FROM CARD

                                   )

                            WHERE order_num = 1;

                            AND cardname = 'VISA';

                         

                        am geting invalid cardname identified am going to pass that card to query this query,i what to be able to pass parameter in cardname

                        • 9. Re: query latest record in table with duplicate
                          Roger

                          if you want to filter on the cardname in the outer-query, then you have to get that column back from your inner-query....

                           

                          SELECT  inactivedate

                                FROM (SELECT inactivedate

                                           , cardname

                                           , ROW_NUMBER() OVER (partition by cardname ORDER BY inactivedate DESC, activedate DESC) AS ORDER_NUM

                                        FROM CARD

                                     )

                              WHERE order_num = 1;

                              AND cardname = 'VISA';

                           

                          hth