All Forums General
deepak.tanna 8 posts Joined 01/12
25 Jul 2014
MAX() OVER () function producing inconsistent results

Hi experts,
Have a SQL that goes something like this

SEL AGREEMENT_ID
		, PDCR_ID
		, OWNR_INSD_ORDER
		, GOVT_ID
		, MAX(GOVT_ID) over (partition by AGREEMENT_ID, PDCR_ID  rows between 1 Following 	and 1 Following) AS INSD_GOVT_ID
		, MAX(GOVT_ID) over (partition by AGREEMENT_ID,PDCR_ID rows between 2 Following 	and 2 Following) AS SCND_INSD_GOVT_ID
--Other Cols
....        
....
FROM ( SEL DISTINCT COL1, 
                                         , COL2,
                                         , AGREEMENT_ID
                                         , PDCR_ID
                                         , OWNR_INSD_ORDER
                                         , GOVT_ID
                                           ...
                                           ...
                FROM TAB1
                INNER JOIN TAB2
                              ON ...
                INNER JOIN TAB3
                              ON ...
                INNER JOIN TAB4
                              ON ...
                LEFT  JOIN TAB5
                              ON ...
             ) AS HLIST
LEFT  JOIN TAB6
            ON ...
LEFT  JOIN TAB7
            ON ...

The data from the inner select returns something like the below along with other cols

AGREEMENT_ID PDCR_ID PRTY_AGMT_RLE_CD OWNR_INSD_ORDER GOVT_ID
7475178 0000401451 INSD      2 123456789
7475178 0000401450 INSD      2 123456789
7475178 0000401451 OWNR      1 123456789
7475178 0000401450 SCND      3 123456789
7475178 0000401450 OWNR      1 123456789

The expected result from the outer select is 

AGREEMENT_ID	PDCR_ID	OWNR_INSD_ORDER	GOVT_ID	INSD_GOVT_ID	SCND_INSD_GOVT_ID
7475178	0000401450	1	123456789	123456789	123456789
7475178	0000401450	3	123456789	123456789	?
7475178	0000401450	2	123456789	?	?
7475178	0000401451	1	123456789	123456789	?
7475178	0000401451	2	123456789	?	?

However the actual result is 

AGREEMENT_ID	PDCR_ID	OWNR_INSD_ORDER	GOVT_ID	INSD_GOVT_ID	SCND_INSD_GOVT_ID
7475178	0000401450	1	123456789	123456789	123456789
7475178	0000401450	3	123456789	123456789	?
7475178	0000401450	2	123456789	?	?
7475178	0000401451	1	123456789	123456789	123456789	
7475178	0000401451	2	123456789	?	?

Where even for PDCR_ID 0000401451 we get a value for SCND_INSD_GOVT_ID.
I reason this would be caused when 3 rows are returned by the inner SQL, but tests show that there are only 2 rows for the AGREEMENT_ID + PDCR_ID combination
Any thoughts on what may be causing the outer MAX() OVER () to return a value for SCND_INSD_GOVT_ID?

 

deepak.tanna 8 posts Joined 01/12
25 Jul 2014

just noticed, the expected results had a typo, pls use the below

AGREEMENT_ID    PDCR_ID OWNR_INSD_ORDER GOVT_ID INSD_GOVT_ID    SCND_INSD_GOVT_ID
7475178 0000401450  1   123456789   123456789   123456789
7475178 0000401450  2   123456789   123456789   ?
7475178 0000401450  3   123456789   ?   ?
7475178 0000401451  1   123456789   123456789   ?
7475178 0000401451  2   123456789   ?   ?

 

dnoeth 4628 posts Joined 11/04
25 Jul 2014

Without ORDER BY the result is random, based on you expected result you probably need:

        , MAX(GOVT_ID) over (partition by AGREEMENT_ID, PDCR_ID
                             ORDER BY OWNR_INSD_ORDER
                             rows between 1 Following and 1 Following) AS INSD_GOVT_ID
        , MAX(GOVT_ID) over (partition by AGREEMENT_ID,PDCR_ID
                             ORDER BY OWNR_INSD_ORDER
                             rows between 2 Following and 2 Following) AS SCND_INSD_GOVT_ID

 

Dieter

deepak.tanna 8 posts Joined 01/12
27 Jul 2014

Thanks Dieter, 
I have included the ORDER BY (as shown below) but still see inconsistent results where sometimes the SCND_INSD_GOVT_ID has the same value as INSD_GOVT_ID, when it should be NULL

,  MAX(GOVT_ID) over (partition by AGREEMENT_ID, PDCR_ID order	by OWNR_INSD_ORDER rows between 1 Following and 1 Following) AS INSD_GOVT_ID
,  MAX(GOVT_ID) over (partition by AGREEMENT_ID,PDCR_ID order	by OWNR_INSD_ORDER rows between 2 Following and 2 Following) AS SCND_INSD_GOVT_ID

 

You must sign in to leave a comment.