我正在尝试编写一个SQL查询,在其中我可以找到出现频率最高的值。
到目前为止,我有这样的想法:
SELECT GENRE, COUNT(*) AS Frequency
FROM BooksRead
GROUP BY GENRE这给出了如下输出:
Anthropological 1
Biography 7
Crime 4
Essay 2 我希望返回的结果是7。我尝试过使用TOP 1,但我的Java编译器似乎不喜欢它。
发布于 2017-08-21 09:11:04
ANSI SQL语法为:
SELECT GENRE, COUNT(*) AS Frequency
FROM BooksRead
GROUP BY GENRE
ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROW ONLY;并非所有数据库都支持该语法。很多人都支持LIMIT
SELECT GENRE, COUNT(*) AS Frequency
FROM BooksRead
GROUP BY GENRE
ORDER BY COUNT(*) DESC
LIMIT 1;但是,确切的语法取决于您使用的数据库。
您还可以使用ANSI标准窗口函数:
SELECT *
FROM (SELECT GENRE, COUNT(*) AS Frequency,
ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as seqnum
FROM BooksRead
GROUP BY GENRE
) g
WHERE seqnum = 1;如果你想要领带,那就用RANK()代替ROW_NUMBER()。
https://stackoverflow.com/questions/45788185
复制相似问题