'Invalid group by expression error when using any_value with max and window function in Snowflake
I was given a query and I am attempting to modify it in order to get the most recent version of each COMP_ID. The original query:
SELECT
ANY_VALUE(DATA_INDEX)::string AS DATA_INDEX,
COMP_ID::string AS COMP_ID,
ANY_VALUE(ACCOUNT_ID)::string AS ACCOUNT_ID,
ANY_VALUE(COMP_VERSION)::string AS COMP_VERSION,
ANY_VALUE(NAME)::string AS NAME,
ANY_VALUE(DESCRIPTION)::string AS DESCRIPTION,
MAX(OBJECT_DICT:"startshape-type")[0]::string AS STARTSHAPE_TYPE,
MAX(OBJECT_DICT:"startshape-connector-type")[0]::string AS STARTSHAPE_CONNECTOR_TYPE ,
MAX(OBJECT_DICT:"startshape-action-type")[0]::string AS STATSHAPE_ACTION_TYPE,
MAX(OBJECT_DICT:"overrides-enabled")[0]::string AS OVERRIDES_ENABLED
FROM COMP_DATA
GROUP BY COMP_ID
ORDER BY COMP_ID;
I then attempted to use a window function to grab only the highest version for each comp_id. This is the modified query:
SELECT
ANY_VALUE(DATA_INDEX)::string AS DATA_INDEX,
COMP_ID::string AS COMP_ID,
ANY_VALUE(ACCOUNT_ID)::string AS ACCOUNT_ID,
ANY_VALUE(COMP_VERSION)::string AS COMP_VERSION,
ANY_VALUE(NAME)::string AS NAME,
ANY_VALUE(DESCRIPTION)::string AS DESCRIPTION,
MAX(OBJECT_DICT:"startshape-type")[0]::string AS STARTSHAPE_TYPE,
MAX(OBJECT_DICT:"startshape-connector-type")[0]::string AS STARTSHAPE_CONNECTOR_TYPE ,
MAX(OBJECT_DICT:"startshape-action-type")[0]::string AS STATSHAPE_ACTION_TYPE,
MAX(OBJECT_DICT:"overrides-enabled")[0]::string AS OVERRIDES_ENABLED,
ROW_NUMBER() OVER (PARTITION BY COMP_ID ORDER BY COMP_VERSION DESC) AS ROW_NUM
FROM COMP_DATA
QUALIFY 1 = ROW_NUM;
When attempting to compile the below error is given:
SQL compilation error: [COMP_DATA.COMP_ID] is not a valid group by expression
I had originally thought the issue was the ANY_VALUE on COMP_VERSION, but after removing the ANY_VALUE the same error was given. The only way I found to not get an error was removing the 4 MAX fields and all of the ANY_VALUE()'s, as shown below:
SELECT
DATA_INDEX::string AS DATA_INDEX,
COMP_ID::string AS COMP_ID,
ACCOUNT_ID::string AS ACCOUNT_ID,
COMP_VERSION::string AS COMP_VERSION,
NAME::string AS NAME,
DESCRIPTION::string AS DESCRIPTION,
ROW_NUMBER() OVER (PARTITION BY COMP_ID ORDER BY COMP_VERSION DESC) AS ROW_NUM
FROM COMP_DATA
QUALIFY 1 = ROW_NUM;
Of course this is not at all sufficient since I need the 4 max fields.
I have also tried creating the table with the max fields and from that new table using the window function to select the highest COMP_VERSION of each COMP_ID, but the same error was given.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
