Skip to content
Advertisement

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

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 :

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 ?

Advertisement

Answer

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:

This returns the overall max of Col_D for each group.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement