Effect of QUALIFY SUM(1) OVER (PARTITION BY ROWS UNBOUNDED PRECEDING)=1?

2
votes

I have two queries almost similar:

SELECT *
FROM table
QUALIFY SUM(1) OVER (PARTITION BY id_field ROWS UNBOUNDED PRECEDING) = 1;

and

SELECT *
FROM table
QUALIFY SUM(1) OVER (PARTITION BY id_field) = 1;

I cannot put my finger on why the ROWS UNBOUNDED PRECEDING has any effect on the queries.

To me, it would seem like both queries would yield the same result, since the function SUM is not using any column of my table, and so ROWS UNBOUNDED PRECEDING would not influence the SUM.

But actually, the first query yields more rows as a result than the second one when I execute it.

What is the difference betwen both queries, and what is the effect of ROWS UNBOUNDED PRECEDING on this particular QUALIFY?

1
sqlteradatawindow-functions
What are you actually trying to do? – Andrew
ROWS UNBOUNDED PRECEDING without order by? – Vamsi Prabhala
I'm trying to get one row for each distinct id_field, based on the most recent date of another column. The original query had an ORDER BY the date field, but I removed it in the question for the sake of focusing on why the number of rows in the results of my queries are different. – user20085
Simply move the SUMs into the Select claus and yo will see the calculated results. – dnoeth

1 Answers

1
votes

The 1st query returns the first row of every partition.
It has a "running total" logic.
The sum for the 1st row will be 1, for the 2nd - 2, for the 3rd - 3 etc.

The 2nd query return rows from partitions that have only a single row.

This will give you the last record of each id_field 

SELECT   *
FROM     table
QUALIFY  row_number() OVER (PARTITION BY id_field order by ... desc) = 1
;