我正在尝试获得一个计数的最大值。代码如下
SELECT coachID, COUNT(coachID)
FROM coaches_awards GROUP BY coachID
HAVING COUNT(coachID) =
(
SELECT MAX(t2.awards)
FROM (
SELECT coachID, count(coachID) as awards
FROM coaches_awards
GROUP BY coachID
) t2
);
然而,某些事情一直在失败。内部查询工作并给出我想要的答案,如果内部查询被所需的数字替换,外部查询将工作。所以我假设我犯了一些语法错误。
我要去哪里错了?
最佳答案
如果您只想查找一行,为什么不这样做:
SELECT coachID, COUNT(coachID) as cnt
FROM coaches_awards
GROUP BY coachID
ORDER BY cnt DESC
LIMIT 1;
如果您想要联系,请使用
RANK()
或DENSE_RANK()
:SELECT ca.*
FROM (SELECT coachID, COUNT(*) as cnt,
RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
FROM coaches_awards
GROUP BY coachID
) ca
WHERE seqnum = 1;
关于hadoop - HiveSQLException:无法识别表达式规范中 'SELECT' 'MAX' '('附近的输入,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/43990489/