SQL Select single categories sorted by popularity

Question

SQL Select single categories sorted by popularity

Navigation

#1 by (1 votes)

0

I'm trying to get a list of categories that are in use and then sort them based on the number of records to be able to assemble a list of categories and use in the site filter. So they should follow these restrictions:

Unique Categories;
Categories that are used;
Sort by popularity (the more records it has, the more top it should be);

For this I have 2 tables, 1 with the list of products and categories:

relacao_categoria
id |  id_categoria | id_produto

And the other one with the list of categories

cadastro_categoria
id  | nome_categoria

At the moment I just could not do select based on popularity because I subsequently need to sort according to the number of records found in the cadastro_produto

This is my sql:

$qry = sql("SELECT DISTINCT id_categoria FROM relacao_categoria");

// Processo para montar a array de id's com o resultado

$qry = sql("SELECT id, nome_categoria FROM cadastro_categoria WHERE id IN ($ids)");

How can I get the results of the 2nd sql to be in order of popularity?

php sql

asked by anonymous 19.05.2016 / 20:40

1 answer

Error trying to save record to bank Help with image upload and preview with $ _SESSION or $ _COOKIE

score 1 · Accepted Answer

The suggestion of Reginaldo Rigo's comment is a good solution. Optionally you can remove the first query sql sql("SELECT DISTINCT id_categoria FROM relacao_categoria"); and make an inner join in the second query.

Using the W3C SQL editor , the query would look like this (I tested it with two tables with different names, categories and products, but that fit your situation):

SELECT count(*) as popularidade, Categories.CategoryID, CategoryName 
from Categories, Products 
WHERE Categories.CategoryID = Products.CategoryID group by
Categories.CategoryID, CategoryName order by count(*) desc;