Problems with "Top N" SQL query

I'm using Elasticsearch 6.7.1. From what I can tell in the documentation, what I want to do should be possible, but it's not working for me:

Here's my query:
SELECT location.country, COUNT(*) AS count FROM my_index GROUP BY location.country ORDER BY count DESC LIMIT 10

This returns:
|location.country |count |
|Australia|2256|
|Argentina|639|
|Albania|46|
|Angola|14|
|Aruba|5|
...

However the top results should be as follows (same query as above but without the LIMIT):
|location.country |count |
|United States|60105|
|China|43388|
|Mexico|25472|
|India|10117|
|United Kingdom|8681|
...

In other words, it appears to be applying the limit before the sort. What am I doing wrong?

@Brian_Frantz this is a bug which has been fixed already and is (should) be available in the versions set as labels in this PR.

1 Like