Consider this simple table of event counts:

I would like to select the top N events by count, but with the additional requirement that if the result set includes any event with a particular count, then it should include all of the events with that count. In other words, I don’t want to break up any of the “groups” of rows that have the same count. Instead, I will potentially get more rows than I asked for.

For example, if I wanted the “top five” events in the table above, the query would actually return six rows so that both events with count 9 were included. The query for the top four would return four rows, and the query for the top three would also return four rows.

How can I accomplish this in SQLite?

CodePudding user response：

One way is to use a common table expression to identify the counts corresponding to the “top five” events:

with top_five as (
    select count from events order by count desc limit 5
)
select * from events where count in top_five order by count desc;

CodePudding user response：

You can use the RANK window function for this task. It's ranking value will be equal for the identical values, but will consider the amount of past rows when needs to assign the next ranking.

WITH cte AS (
    SELECT *, RANK() OVER(ORDER BY count_) AS rn
    FROM events
)
SELECT event_name, count_ FROM cte WHERE rn <= 5

Check the demo here.