How to Sort Records After Count

Navigation
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 amount of leads. Those who have more, stay in first. But he returns me with the wrong Ranking number.

This happens because it puts the number before it counts.

    
asked by anonymous 19.10.2016 / 16:00

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
    
19.10.2016 / 22:48
1
order by 1
 //se quiser ordenar por "rank"

ou 

order by 10
//se quiser ordenar por "qtd_leads"
    
19.10.2016 / 16:12
List of View objects for controller Get Variable from one Config directly to another Config