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 ?
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:
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.