Using columns in window function that are not in Group By, I get : SQL compilation error: [COLUMN_A] is not a valid group by expression

Question

I've seen a lot of questions about this error, the closest one (as it's using window function) to my issue would be this one

My issue is that I use columns in the windows function that are not grouped by :

SELECT
    Col_A,
    Col_B,
    FIRST_VALUE(Col_C) OVER (PARTITION BY Col_A, Col_B
                            ORDER BY Col_TimeStamp ASC 
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    MAX(Col_D) OVER (PARTITION BY Col_A, Col_B
                            ORDER BY Col_TimeStamp ASC
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM mytable
    GROUP BY 
        Col_A,
        Col_B

With that query, I get the error for Col_C, Col_D, Col_TimeStamp

SQL compilation error: [eachColumn] is not a valid group by expression

I know I can "make the query work" by adding everything into the GROUP BY clause, but it seems to defeat the purpose as I'm using a kind of timestamp to Order By.

I have a huge data set and can't easily check if it really does defeat the purpose.

Is it just me being paranoid or do I miss something ?

Please provide sample data and desired results. The `first_value()` makes sense, but the `max()` does not in an aggregated query. — Gordon Linoff, Apr 08 '21 at 15:29

score 1 · Accepted Answer · answered Apr 08 '21 at 15:30

The first_value() makes sense because there is only one value per group. However, the max() does not make sense.

I wonder if this does what you really want:

SELECT DISTINCT Col_A, Col_B,
       FIRST_VALUE(Col_C) OVER (PARTITION BY Col_A, Col_B
                                ORDER BY Col_TimeStamp ASC 
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                               ),
       MAX(Col_D) OVER (PARTITION BY Col_A, Col_B)
FROM mytable;

This returns the overall max of Col_D for each group.

Thx, you're right ! I'm refactoring some PySpark to Snowflake and put the group by to define my window frames, but as I can't use them I deleted them but let the group by... :/ — R3uK, Apr 08 '21 at 15:54

Using columns in window function that are not in Group By, I get : SQL compilation error: [COLUMN_A] is not a valid group by expression

1 Answers1