查询在MySQL中运行良好，给出的'in select子句既不是聚合也不是group by子句‘雪花中的错误

Question

MySQL查询：

SELECT soc_pp_code, COUNT(soc_pp_code) AS count, rate
FROM subscriptions_history_sample_anonymized
GROUP BY soc_pp_code 
ORDER BY COUNT(soc_pp_code) DESC 
LIMIT 1;

​

​

雪花查询：

SELECT soc_pp_code, COUNT(soc_pp_code) AS count, rate
FROM subscriptions_history_sample_anonymized
GROUP BY 1
ORDER BY 2  
LIMIT 1;

错误: SQL编译错误: select子句中位置49 'SUBSCRIPTIONS_HISTORY_SAMPLE_ANONYMIZED.RATE‘处的错误第1行既不是聚合，也不是group by子句。

Answer 1

就像错误message3说的那样，每一列都必须是分组的，或者使用聚集函数 (如MIN )作为速率或任何您需要的东西。

SELECT soc_pp_code, COUNT(soc_pp_code) AS count, MIN(rate)
FROM subscriptions_history_sample_anonymized
GROUP BY 1
ORDER BY 2  
LIMIT 1;