CODEWITHSUNDEEP
phpmysqlsql
TN888
TN888

Reputation: 7739

Get the list of ten top publishers in database

I've got following tables in my MySQL database:

USERS

iduser nick password

ARTICLES

idarticles iduser text

How can I get by one SQL query the list of e.g. 10 top publishers of articles ordering by the count of added articles? Is there any way to do that? I'm using PHP to ask database.

Upvotes: 0

Views: 45

Answers (1)

kero
kero

Reputation: 10638

Yes, this should be quite easy via JOIN and COUNT(). Something like the following

SELECT `users`.`iduser`, COUNT(`articles`.`idarticles`) AS `total_articles`
FROM `users`
INNER JOIN `articles` ON `users`.`iduser` = `articles`.`iduser`
GROUP BY `users`.`iduser`
ORDER BY `total_articles` DESC
LIMIT 10

A little explaining:

  • COUNT() will get you what it says - a count of all relevant entries in articles
  • INNER JOIN will pair all entries that belong together (defined via ON)
  • GROUP BY tells SQL that you are interested in various results, each differing by iduser (without this, you'd get all articles counted in the first returned row)
  • ORDER BY .. DESC is important to get the result in a descending order (so most published first)
  • LIMIT 10 does exactly that

Upvotes: 3

Related Questions