How to Sort Records After Count

Asked

Viewed 312 times

1

Query

SET @ranking = 0;

SELECT
    @ranking := @ranking + 1 AS rank,
    `empresas`.`id`,
    `empresas`.`id` AS `id_empresa`,
    `empresas`.`id_marca`,
    `slug`,
    `slug_bairro`,
    `empresa_logins`.`nome`,
    `empresas`.`id_cidade`,
    `empresas`.`empresa`,
    COUNT(empresa_leads.id) AS qtd_leads
FROM
    `empresa_leads`
RIGHT JOIN `empresas` ON `empresas`.`id` = `empresa_leads`.`id_empresa`
LEFT JOIN `empresa_login_admins` ON `empresa_login_admins`.`id_empresa` = `empresas`.`id`
AND `empresa_login_admins`.`id_funcao` = 11
AND `empresa_login_admins`.`id_categoria` IS NULL
LEFT JOIN `empresa_logins` ON `empresa_logins`.`id` = `empresa_login_admins`.`id_empresa_login`
WHERE
    `empresa_leads`.`deleted_at` IS NULL
AND `empresas`.`id_marca` = 11
AND empresas.`estado` = 26
GROUP BY
    `empresas`.`id`
ORDER BY
    qtd_leads desc, rank

Return

Rank                        Qtd_Leads
49        12291 12291 11       15
1         12243 12243 11       0
2         12244 12244 11       0
3         12245 12245 11       0
4         12246 12246 11       0

I need the ranking to be done by the number of leads. Who has more, stay first. But it returns me with the wrong ranking number.

This is because he puts the number before the Count.

mysql

2 answers

1


 SELECT * FROM 
  (SELECT
        @ranking := @ranking + 1 AS rank,
        `empresas`.`id`,
        `empresas`.`id` AS `id_empresa`,
        `empresas`.`id_marca`,
        `slug`,
        `slug_bairro`,
        `empresa_logins`.`nome`,
        `empresas`.`id_cidade`,
        `empresas`.`empresa`,
        COUNT(empresa_leads.id) AS qtd_leads
    FROM
        `empresa_leads`
    RIGHT JOIN `empresas` ON `empresas`.`id` = `empresa_leads`.`id_empresa`
    LEFT JOIN `empresa_login_admins` ON `empresa_login_admins`.`id_empresa` = `empresas`.`id`
    AND `empresa_login_admins`.`id_funcao` = 11
    AND `empresa_login_admins`.`id_categoria` IS NULL
    LEFT JOIN `empresa_logins` ON `empresa_logins`.`id` = `empresa_login_admins`.`id_empresa_login`
    WHERE
        `empresa_leads`.`deleted_at` IS NULL
    AND `empresas`.`id_marca` = 11
    AND empresas.`estado` = 26
    GROUP BY
        `empresas`.`id`
    ORDER BY
        qtd_leads desc
    ) AS results
    ORDER BY results.rank

1

order by 1
 //se quiser ordenar por "rank"

ou 

order by 10
//se quiser ordenar por "qtd_leads"

  • Don’t order right. I want to order by qtd_leads and then rank to be right.

  • can recur to subquery

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.