Greenplum SQL related question

gyambqt — Thu, 28 May 2020 10:58:10 GMT

Hi Experts,

I have a SQL as shown below:

select stg.* from(
select stg.ID, stg.revision_dttm, stg.no,date, stg.value,
row_number() over(partition by stg.ID order by stg.value desc,
stg.date desc) as tmp_row
from table1 stg
) stg

inner join table2 tref1
on stg.ID = tref1.ID and
stg.revision_dttm = tref1.revision_dttm
and tref1.id2='1'

each time when I run script above I was getting different result for tmp_row. why is this happening?

run1:

ID revision_dttm no value date tmp_row
1 2020-02-14 08:12:49 a 0 2020-01-20 00:00:00 1
1 2020-02-14 08:12:49 b 0 2020-01-20 00:00:00 2

run2:

ID revision_dttm no value date tmp_row
1 2020-02-14 08:12:49 a 0 2020-01-20 00:00:00 2
1 2020-02-14 08:12:49 b 0 2020-01-20 00:00:00 1

Re: Greenplum SQL related question

JBailey — Thu, 28 May 2020 17:45:00 GMT

Hi @gyambqt

Just a guess.

The ORDER BY clause sorts on stg.value and stg.date. These values are the same in your output. So, the sort is coming into the PARTITION clause in a different order but is still correct. Greenplum is a highly parallel database. Nodes could be producing intermediate result sets at different times. Thus, producing a random assignment of the tmp_row value.

Best wishes,

@JBailey

topic Re: Greenplum SQL related question in SAS Programming

Greenplum SQL related question

Re: Greenplum SQL related question