Home » SQL & PL/SQL » SQL & PL/SQL » Adding in a MAX() function (Oracle 9i)  () 1 Vote
Adding in a MAX() function [message #352223] Mon, 06 October 2008 12:41 Go to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Dumb question, I have a query that (loosely) looks like the following:

select 
     t1.field1, 
     t2.field2
from 
     table1 t1, 
     table2 t2
where t1.item = t2.item (+)
  and ((t1.str = 'blah') or (t1.str = 'notblah' AND t2.b = 'N')
  and ((t2.prod_id_c = 'HW' AND 
          (upper(t2.emrg_c) = 'YES' OR upper(t2.stat_call_c) = 'YES'))
        OR t2.prod_id_c != 'HW')


The problem with my query is that in table2 (t2), there are multiple records per item, and we only want one. I want to ONLY select records from this table that have a maximum value in a "lst_upd_ts" field (i.e. the latest updated row). I would think this is easy as adding in an "AND t2.lst_upd_ts = (select max(lst_upd_ts) from table2 ..)" but it needs to comprise all the existing logic above, and I don't know of an easy to way to encorporate all the other logic surrounding the table2.

Any thoughts? Hopefully I've explained it well enough, I'm thinking there is a function to do this easily, but I'm unfamiliar. Thanks in advance!

Is there an easy way to
Re: Adding in a MAX() function [message #352228 is a reply to message #352223] Mon, 06 October 2008 13:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Also search for "Top N".

Regards
Michel
Re: Adding in a MAX() function [message #352439 is a reply to message #352228] Tue, 07 October 2008 11:50 Go to previous messageGo to next message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Thanks so much for the reply. I've looked into those functions, and I fully understand their logic. However, I'm still having a difficult time using them to bring back only 1 row from that one table. Sad

I can select the rankings and get the row-numbers to see they are different, and order them however needed, but I'm still confused as to how to make it only return the "top" one that I'm getting, since I order the duplicate rows by the timestamp.

Any other words to guide me closer to a solution for this? I really appreciate the help!
Re: Adding in a MAX() function [message #352490 is a reply to message #352439] Wed, 08 October 2008 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can select the rankings and get the row-numbers to see they are different, and order them however needed, but I'm still confused as to how to make it only return the "top" one that I'm getting

When you have the row number/rank then restrict the return rows to those with rank 1.

Regards
Michel
Re: Adding in a MAX() function [message #352626 is a reply to message #352490] Wed, 08 October 2008 11:29 Go to previous message
svguerin3
Messages: 44
Registered: November 2005
Location: TX
Member

Thanks for the helpful advice. I think I've found the way I should be doing it - I'm basically replacing the whole table in the FROM statement with a separate select statement that only grabs the top-ranked ones, as you've specified. Problem is now the query that usually runs 5 minutes runs about 7-8 hours!

I have attached the explain plan from SQLNav (it's Oracle 9i, RULE-based optimizer - no changing that unfortunately). I'm really not good at reading these, so any advice would be greatly appreciated on starting points for figuring out why this thing is running so long now.

Thanks again, and I look forward to hearing any responses.

Edit: query copied below if needed, important part to note is the "-- NEW LOGIC" lines:

SELECT DISTINCT
       os.ord_id ord_id,                            
       nvl(trim(os.client_nm),ci.clnt_nm) clnt_nm,  
       os.client_id_n client_id_n,                  
       os.user_def1_x user_def1_x,                  
       concat((substr(os.fst_nm,1,(instr(os.fst_nm,' ',1,1)))),(ltrim(os.lst_nm,' '))) fst_lst_nm, 
       nvl(disp.email_addr_x,trim(co.email_addr_x)) email_addr_x, 
       os.cpgn_c cpgn_c,                            
       wi.prod_id_c prod_id_c,                      
       disp.disp_st_c disp_st_c,                    
       disp.disp_d disp_d,                         
       disp.chan_type_c chan_type_c,               
       os.cpgn_st_c cpgn_st_c,                     
       disp.bounce_stat_c bounce_stat_c,           
       co.pref_sel_i pref_sel_i,                   
       c.comu_desc_x comu_desc_x,                  
       wi.emrg_c emrg_c,                           
       os.sel_d sel_d,                                          
       wi.comu_type_c 
       comu_type_c,                 
       decode(disp.bounce_stat_c,
           'HARD','BOUNCE',
           'SOFT','BOUNCE',
           decode(os.cpgn_st_c,
               'UNDEL',
                decode(co.pref_sel_i,
                   'N','PREFERENCE',
                   decode(trim(co.email_addr_x),
                       null,'MISSING',
                       decode(wi.comu_type_c,
                           '3PTY','3PTY'
                           )
                       )
                   )
               )
           ) as "Undel/Del Details",               
       wi.nb_accs_c nb_accs_c,                     
       wi.crea_node_nm crea_node_nm,                          
       wi.stat_call_c stat_call_c                 
FROM   (
       SELECT /*+ CHOOSE FIRST_ROWS +*/
              os.ord_id ord_id,
              os.client_nm client_nm,
              os.client_id_n client_id_n,
              os.user_def1_x user_def1_x,
              os.fst_nm fst_nm,
              os.lst_nm lst_nm,
              os.cpgn_c cpgn_c,
              os.cpgn_st_c cpgn_st_c,
              os.sel_d sel_d
       FROM   t_fbsos_offr_stg os
       WHERE  os.sel_d >= (trunc(sysdate) - 1)
       ) os,
       (
       SELECT /*+
              CHOOSE ALL_ROWS
              +*/
              DISTINCT
              c.campaigncode campaigncode,
              com.comu_desc_x comu_desc_x
       FROM   afinprod.ua_campaign c,
              afinprod.ua_campaignextattr cea,
              t_fbcfc_comu com,
              (
              SELECT /*+
                     CHOOSE ALL_ROWS
                     +*/
                     comh.comu_hier_grp_id comu_hier_grp_id
              FROM   t_fbcch_comu_hier comh
              START
              WITH   comh.desc_x = 'XTRAC'
              CONNECT
              BY     PRIOR comh.comu_hier_grp_id = comh.prnt_comu_hier_grp_id
              ) comh
       WHERE  c.campaignid = cea.campaignid
       AND    cea.fesco_comu_id = com.fesco_comu_id
       AND    com.comu_desc_x IN (
                  'SMC ACTIVITY','SMC INITIAL STATUS E-MAIL','SMC FOLLOW-UP STATUS E-MAIL','SMC RESOLUTION'
                  )
       AND    com.comu_hier_grp_id = comh.comu_hier_grp_id
       ) c,
       t_fbhhd_cont_hist_disp disp, 
       t_fbsco_chan_ord co, 
         (select * from t_fbcwi_chnl_wrk_item tbl1,                 -- NEW LOGIC
       (select WRK_ITM_CNFRM_N as tbl2wkItem,                       -- NEW LOGIC
        MAX(LST_UPD_TS) as tbl2LST_UPD_TS                           -- NEW LOGIC
        from t_fbcwi_chnl_wrk_item                                  -- NEW LOGIC
        group by WRK_ITM_CNFRM_N) tbl2                              -- NEW LOGIC
        WHERE  tbl1.WRK_ITM_CNFRM_N = tbl2.tbl2wkItem               -- NEW LOGIC
        and tbl1.LST_UPD_TS = tbl2.tbl2LST_UPD_TS)  wi,             -- NEW LOGIC
       t_fici_clnt_info ci
WHERE  ((os.sel_d >= (trunc(sysdate) - 1) 
AND     ((disp.disp_st_c = 'MAILED')                                                             
OR       (disp.disp_st_c = 'FAILED' AND disp.bounce_stat_c IN ('HARD','SOFT'))                   
OR       (os.cpgn_st_c = 'UNDEL' AND wi.comu_type_c = '3PTY')                                    
OR       (os.cpgn_st_c = 'UNDEL' AND co.pref_sel_i = 'N')                                        
        )
       )
OR     (os.sel_d >= trunc(sysdate)  AND os.cpgn_st_c = 'UNDEL' AND trim(co.email_addr_x) IS NULL 
       )
      )
AND   os.ord_id = disp.ord_id (+)
AND   os.ord_id = co.ord_id
AND   os.user_def1_x = wi.wrk_itm_cnfrm_n (+)
AND   os.client_id_n = ci.clnt_id_n (+)
AND   os.cpgn_c = c.campaigncode
   AND ((wi.prod_id_c = 'HW' AND 
          (upper(wi.emrg_c) = 'YES' OR upper(wi.stat_call_c) = 'YES'))
        OR wi.prod_id_c != 'HW')



  • Attachment: ep.JPG
    (Size: 144.28KB, Downloaded 77 times)

[Updated on: Wed, 08 October 2008 11:49]

Report message to a moderator

Previous Topic: Timestamp Primary key is giving error while inserting.
Next Topic: multiple rows in one column
Goto Forum:
  


Current Time: Sun Dec 04 22:51:05 CST 2016

Total time taken to generate the page: 0.25031 seconds