0

Guy I am trying to ranking some data from my database, and I notice that it's going very wrong when I put the group by clause;

SET @rank=0;

SELECT @rank:=@rank+1 AS RankSemGenero
    ,a.nome AS Artista
    ,f.nome AS Musica
    ,SUM(rnk.total) AS Tocadas
    ,rnk.mes AS Mes
    ,rnk.dia AS Dia
    ,current_timestamp() AS Criado_Em_Sem_Genero
    ,23 AS RankComGenero
    ,current_timestamp() AS Criado_Em_Com_Genero
    /*,CASE rnk.categoria
        WHEN 1 then 'AM'
        WHEN 2 then 'FM'
        WHEN 3 then 'Web'
        WHEN 4 then 'Comunitaria'
    END AS Categoria_Radio*/
    ,'Todas' AS TipoEmissora
    ,5 AS Relevancia_Emissora
    ,'Nacional' AS Local
    ,5 AS Relevancia_Local
    ,1 AS fl_ativo
FROM rnk201901 rnk
LEFT JOIN artistas a ON rnk.artista = a.id
LEFT JOIN fonogramas f ON rnk.fonograma = f.id
WHERE rnk.dia = 10
-- AND rnk.fonograma = 35876
-- GROUP BY rnk.fonograma
ORDER BY rnk.total DESC;

This code above bringing the information on the right way 1 until ....

But if I change the GROUP BY line, I am receiving something like: 1700 instead of 1.

GROUP BY rnk.fonograma

Any idea how to handle this group by counting 1 by 1?

Thanks!!

  • Use GROUP BY in a subquery, and calculate rank (with proper ordering) in outer query. – Akina Jan 29 at 18:02
  • probably I am doing wrong way, because when i try to create a subquery, is bringing an information as "Error Code: 1242. Subquery returns more than 1 row" – Oracy Martos Jan 29 at 18:08
  • Subquery must be in FROM section. Instead of your tableset. – Akina Jan 29 at 18:12
  • 1
    With MySQL-8.0 / MariaDB-10.2 - window functions provide a easy answer – danblack Jan 29 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.