CODEWITHSUNDEEP
phpsqlalgorithmsorting
algorithmicCoder
algorithmicCoder

Reputation: 6789

Optimal SQL statement or algorithm for finding Most Popular Category of Items in a Database

Assume an element e.g. a youtube video can be of various categories e.g. music, sports, entertainment.

I want to look through all elements in a database and output an ordered list of the most frequently occurring categories.

EDIT: To be specific, There are three relevant database tables. 1) item (name, id) 2) category (name, id) 3) item_category (item_id, category_id)

1) Is there anything more efficient than this?:

Pseudocode
foreach (category)
   select all database elements which match that category
   associate category with the number of results from the select query
   return the associative array

 sort the associative array on the values of each category (key)

2) What PHP function allows me easily do the sorting of the values of an associative array? Or what SQL allows me do this more efficiently?

Upvotes: 2

Views: 281

Answers (1)

sdfadfaasd
sdfadfaasd

Reputation: 1446

SELECT COUNT(*) AS popularity
FROM categories, views
WHERE views.category_id = categories.id
GROUP BY views.category_id
ORDER BY popularity DESC

This selects all the categories and orders them from most to least popular.

Upvotes: 2

Related Questions