Selecting top 10 counts in SQLite

Question

I have a table which records questions, their answers, and their authors. The column names are as follows:

id, question, answers, author

I would like to get a list of top 10 authors who have written the most questions. So it would need to first count the number of questions each author has written then sort them by the count then return the top 10.

This is in SQLite and I'm not exactly sure how to get the list of counts. The second part should be fairly simple as it's just an ORDER BY and a LIMIT 10. How can I get the counts into a list which I can select from?

Answer 1

SELECT BY COUNT(author)
    ,author
FROM table_name
GROUP BY author
ORDER BY COUNT(author) DESC LIMIT 10;