Assuming I have two tables in my database ( pedidos and pedidos_itens ) how can I get the data from the orders sorted by the orders that contain the most items?
I have tried to do a Right Join , but I do not know what to use in the order by clause, since my goal is to sort by the number of records in the% .
Try this:
SELECT
COUNT(pi.*) AS itens,
p.id AS id,
p.descricao AS desc
FROM pedido p
INNER JOIN pedido_itens pi ON
pi.id_pedido = p.id
GROUP BY id, desc
ORDER BY itens ASC
The idea is to give COUNT of the amount of items returned to the request, group the registry data in GROUP BY and ORDER BY by COUNT .