5

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?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
null0pointer
  • 1,533
  • 3
  • 15
  • 29
  • Maybe you want a [WITH statement](https://www.sqlite.org/lang_with.html), which would allow you to `SELECT` from a psuedo-table for the duration of your query? In order to get the actual list of counts, look into `GROUP BY` and the `COUNT` statement/function. – tew Aug 19 '15 at 20:53

3 Answers3

6
SELECT BY COUNT(author)
    ,author
FROM table_name
GROUP BY author
ORDER BY COUNT(author) DESC LIMIT 10;
FutbolFan
  • 13,235
  • 3
  • 23
  • 35
MHardwick
  • 659
  • 3
  • 9
3

You can apply an order by clause to an aggregate query:

SELECT   author, COUNT(*)
FROM     mytable
GROUP BY author
ORDER BY 2 DESC
LIMIT    10
Mureinik
  • 297,002
  • 52
  • 306
  • 350
3

You could wrap your query as a subquery and then use LIMIT like this:

SELECT *
FROM (
    SELECT author
        ,COUNT(*) AS cnt
    FROM mytable
    GROUP BY author
    ) t
ORDER BY t.cnt DESC 
LIMIT 10;
FutbolFan
  • 13,235
  • 3
  • 23
  • 35