3 Replies Latest reply: Mar 18, 2014 6:25 AM by K.S.I. RSS

    Selecting records with max(date) from the same table

    470436

      I have a table Customer and customer interaction

      A simple query to find the last interaction date of a customer

      Select cu.custno, cu.custref,ci.interaction_date, ci.status
      from customer cu ,
           cust_interaction ci
      where cu.custno = ci.custno
      and ci.interaction_dat = (select max(interaction_date) from ci1 where ci1.interaction_date = ci.interaction_date)

       

      I am not exactly feeling great about this max being used.

       

      can u plz guide me in using the best method

       

      with warm regards

      ssr

        • 1. Re: Selecting records with max(date) from the same table
          AnnPricks E

          Try this?

          SELECT custno,

                 custref,

                 interaction_date,  

                 status

          FROM

          (SELECT cu.custno custno,

                  cu.custref custref,

                  ci.interaction_date interaction_date,

                  DENSE_RANK() OVER(ORDER BY ci.interaction_date DESC) rn,      

                  ci.status status

          FROM customer cu ,

               cust_interaction ci

          WHERE cu.custno = ci.custno)

          WHERE rn =  1;

          • 2. Re: Selecting records with max(date) from the same table
            Roger

            imho

             

            nothing wrong with you solution - is there an index on interaction_date ?

             

            hth

            • 3. Re: Selecting records with max(date) from the same table
              K.S.I.

              Hi.

               

              other option with use MAX(..) KEEP (...)

               

              rem custom_inter

              select 1 id ,sysdate - level/24 interaction_date , case when mod(level,2)=0 then 'Worker' else 'Closed' end status

              from dual connect by level < 7

              union all

              select 2 id ,sysdate - level/24 interaction_date ,  case when mod(level,2)!= 0 then 'Worker' else 'Closed' end status

              from dual connect by level < 7

              order by 1,2 desc;

              rem based script

              with

              customer 

              as

              (select  1 id  from dual union all

              select  2   from dual

              )

              ,custom_inter as

              (

              select 1 id ,sysdate - level/24 interaction_date , case when mod(level,2)=0 then 'Worker' else 'Closed' end status

              from dual connect by level < 7

              union all

              select 2 id ,sysdate - level/24 interaction_date ,  case when mod(level,2)!= 0 then 'Worker' else 'Closed' end status

              from dual connect by level < 7

              )

              select

                   c.id                                                                        id,

                   max(ci.interaction_date) keep(dense_rank last order by ci.interaction_date) interaction_date,

                   max(ci.status) keep(dense_rank last order by ci.interaction_date)           status

              from customer c,custom_inter ci

              where ci.id =c.id

              group by c.id;

               

              SQL>

               

              SQL>

               

                      ID INTERACTION_DATE STATUS

              ---------- ---------------- ------

                       1 18.03.2014 14:19 Closed

                       1 18.03.2014 13:19 Worker

                       1 18.03.2014 12:19 Closed

                       1 18.03.2014 11:19 Worker

                       1 18.03.2014 10:19 Closed

                       1 18.03.2014 9:19: Worker

                       2 18.03.2014 14:19 Worker

                       2 18.03.2014 13:19 Closed

                       2 18.03.2014 12:19 Worker

                       2 18.03.2014 11:19 Closed

                       2 18.03.2014 10:19 Worker

                       2 18.03.2014 9:19: Closed

               

              12 rows selected

               

                      ID INTERACTION_DATE STATUS

              ---------- ---------------- ------

                       1 18.03.2014 14:19 Closed

                       2 18.03.2014 14:19 Worker