Jump to content
Sign in to follow this  
p_sony_2000@yahoo.com

Max of a value among rows

Recommended Posts

Hi,

 

I have the below data in one of our Materialized View

 

R879_PH1_SEQ_NUM R879_PH1_WSLR_CO_ID R879_PH1_RTLR_CO_ID R879_PD1_SEQ_NUM R879_PD1_DATE_QUAL R879_PD1_DATE_CALC R879_PD1_UPC_CASE R879_PD1_UPCEAN_PC R879_PD4_SEQ_NUM ORIG_R879_PD4_MAC_QUAL R879_PD4_MAC_ID CONSUMER_NUM CASE_NUM

199714 1000001887 1000000558 32938719 07' 12/28/2015 18200960468 18200001741 48583194 5 02N03 18200001741 18200960468

193703 1000001887 1000000558 32373409 07' 9/26/2016 18200960468 18200001741 47911465 5 02N03 18200001741 18200960468

200335 1000001887 1000000558 33049290 07' 9/26/2016 18200960468 18200001741 48732282 5 02N03 18200001741 18200960468

 

I want to get the max of R879_PH1_SEQ_NUM along with other columns of the corresponding max r879_PH1_seq_num row.

I used the below query which uses co-related subqueries, but the performance is bad for all data.

 

SELECT mvup.r879_ph1_seq_num AS pl_r879_ph1_seq_num,

mvup.r879_pd1_seq_num AS pl_r879_pd1_seq_num,

mvup.r879_ph1_wslr_co_id AS pl_r879_ph1_wslr_co_id,

mvup.r879_ph1_rtlr_co_id AS pl_r879_ph1_rtlr_co_id,

mvup.r879_pd1_upcean_pc AS pl_r879_pd1_upcean_pc,

mvup.r879_pd1_upc_case AS pl_r879_pd1_upc_case,

mvup.orig_r879_pd4_mac_qual AS pl_r879_pd4_mac_qual,

mvup.r879_pd4_mac_id AS pl_r879_pd4_mac_id,

mvup.r879_pd1_date_calc AS pl_r879_pd1_start_date,

ADD_MONTHS (mvup.r879_pd1_date_calc, 24)

AS pl_r879_pd1_end_date

FROM unauthorized_products mvup

WHERE mvup.r879_ph1_rtlr_co_id = 1000000558

AND EXISTS

(SELECT 1

FROM r879_ph1 ph1

WHERE ph1.r879_ph1_seq_num =

mvup.r879_ph1_seq_num

AND ph1.r879_ph1_doc_type = 'PRC')

AND mvup.R879_PD1_DATE_QUAL = '07'

AND TRUNC (mvup.r879_pd1_date_calc) < TRUNC (SYSDATE + 1)

AND mvup.R879_PH1_SEQ_NUM =

(SELECT MAX (b.R879_PH1_SEQ_NUM)

FROM unauthorized_products b

WHERE mvup.r879_ph1_rtlr_co_id =

b.r879_ph1_rtlr_co_id

AND mvup.r879_ph1_wslr_co_id =

b.r879_ph1_wslr_co_id

AND mvup.consumer_num = b.consumer_num

AND mvup.case_num = b.case_num

AND mvup.ORIG_R879_PD4_MAC_QUAL =

b.ORIG_R879_PD4_MAC_QUAL

AND NVL (mvup.R879_PD4_MAC_ID, ' ') =

NVL (b.R879_PD4_MAC_ID, ' ')

AND b.R879_PD1_DATE_QUAL = '07'

AND TRUNC (mvup.r879_pd1_date_calc) <

TRUNC (SYSDATE + 1)

)

 

I need to tune this query or use analytical function which is faster in performance. Can you please help me in modifying the query in using analytical function

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×