MAX() OVER () function producing inconsistent results

General
Teradata Employee

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?


Tags (1)
3 REPLIES
Teradata Employee

Re: MAX() OVER () function producing inconsistent results

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 ? ?
Junior Contributor

Re: MAX() OVER () function producing inconsistent results

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
Teradata Employee

Re: MAX() OVER () function producing inconsistent results

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