0

I had a spreadsheet that looked like a prior "group by" had left many rows blank where I needed them to be filled with the data above it (see example picture below). I needed each account number to fill all the cells beneath it until the start of the next account number (i.e., A1234 needs to be in all the cells up to B4325, B4325 needs to be in all the cells up to C3452 and so on).

enter image description here

From this stack exchange answer by benjamin berhault I found this code and tailored it to my problem:

SELECT rn, acct, FIRST_VALUE(acct) OVER(PARTITION BY grp)
FROM (SELECT rn, acct, SUM(CASE WHEN acct <> '' THEN 1 END) OVER (ORDER BY rn) AS grp
        FROM 
        (SELECT ROW_NUMBER() OVER() rn
         , acct
         FROM dataset AS d) AS sub1 ) sub2;

What I don't understand about this query is the ORDER BY clause in this part

SUM(CASE WHEN acct <> '' THEN 1 END) OVER (ORDER BY rn) AS grp

This whole line works to successfully create a new grp column that is all 1's for the first account, all 2's for the second account and so on. From here it can use the FIRST VALUE PARTITION BY in the main query to get the result I am looking for, but what I do not understand is why does ORDER BY rn cause the column to sum in that manner? I would have thought a PARTITION BY would be needed there, but it does not work.

3
  • 3
    Your sub1 expression has no ordering so the results of the whole statement might change. Commented Jan 6, 2021 at 16:13
  • @clamp I need it to work in the order that the data comes in and I have to do this for multiple fields. If I use an ORDER BY for each field in sub1, I think it may put the fields out of sync. Please correct me if I'm wrong, but leaving it as is it should pull from the dataset table in the order the table is in, right? Commented Jan 7, 2021 at 11:22
  • No, the order of your rows might change if you do some updates. You need a timestamp or a serial if you want to maintain the order of your data coming in. Commented Jan 7, 2021 at 13:33

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.