I spotted something interesting about this query. The following [executes in about 47ms](https://global-power-plants.datasettes.com/global-power-plants?sql=select+country_long%2C+count%28*%29%0D%0Afrom+%28%0D%0A++select+*+from+%5Bglobal-power-plants%5D+order+by+rowid%0D%0A%29%0D%0Awhere+country_long+is+not+null%0D%0Agroup+by+country_long%0D%0Aorder+by+count%28*%29+desc) (against a table containing 33,000 rows):
select country_long, count(*)
from (
select * from [global-power-plants] order by rowid
)
where country_long is not null
group by country_long
order by count(*) desc
Note that there's an `order by rowid` in that inner query which is unnecessary - the outer `group by` query doesn't need to care about the order of the rows in that inner query.
That same query again, without the unnecessary `order by rowid`:
select country_long, count(*)
from (
select * from [global-power-plants]
)
where country_long is not null
group by country_long
order by count(*) desc
This executes [in 7ms](https://global-power-plants.datasettes.com/global-power-plants?sql=select+country_long%2C+count%28*%29%0D%0Afrom+%28%0D%0A++select+*+from+%5Bglobal-power-plants%5D%0D%0A%29%0D%0Awhere+country_long+is+not+null%0D%0Agroup+by+country_long%0D%0Aorder+by+count%28*%29+desc) against the same table, returning the same results.
Would it be possible for SQLite to notice that the inner `order by` is unnecessary and ignore it when executing this kind of query?
(The reason I'm using a nested query here is that my software is designed to be able to calculate "facet counts" against the results of any arbitrary SQL query, which this pattern lets me do.)