How to do a ranking with SQL?

Question

How to do a ranking with SQL?

Navigation

#1 by (1 votes)

1

Well, I have a "single votes" table that receives a "BOOLEAN" value in the columns "single vote1", "single vote2" and "single vote3", where 1 means that the person voted in that uniform and 0 otherwise the logical image of the table:

Withthiscommand:

SELECT(SELECTCOUNT(voto_uniforme1)FROMvotos_uniformeWHEREvotos_uniforme.voto_uniforme1=1)ASUniforme1,(SELECTCOUNT(voto_uniforme2)FROMvotos_uniformeWHEREvotos_uniforme.voto_uniforme2=1)ASUniforme2,(SELECTCOUNT(voto_uniforme3)FROMvotos_uniformeWHEREvotos_uniforme.voto_uniforme3=1)ASUniforme3

Itreturnsmethis:

But I need it to show me how a ranking, for example who has the highest number of votes comes first, who has the highest amount among the other two comes in second, and the lowest comes last in third, which would be the best way to do this?

mysql

asked by anonymous 17.12.2017 / 15:20

1 answer

Creation of variables Adapt transparency code in this PHP code

score 1 · Accepted Answer

Make a label for you to have the name of the uniform and its quantity and join the 3 selections with UNION ALL , then create a select for ordering the quantity, example strong>:

SELECT * FROM (
SELECT COUNT(voto_uniforme1) as quantidade, 'uniforme1' as uniforme 
        FROM votos_uniforme WHERE votos_uniforme.voto_uniforme1 = 1
UNION ALL
SELECT COUNT(voto_uniforme2) as quantidade, 'uniforme2' as uniforme 
        FROM votos_uniforme WHERE votos_uniforme.voto_uniforme2 = 1
UNION ALL
SELECT COUNT(voto_uniforme3) as quantidade, 'uniforme3' as uniforme 
        FROM votos_uniforme WHERE votos_uniforme.voto_uniforme3 = 1)
AS t ORDER BY quantidade desc

In this case you will generate 3 rows with ordering by quantity, different from the one that generated only one line.

To create the View it must be without the SELECT more external (it does not accept the creation of View with Sub Query ), creation example:

CREATE VIEW 'db'.'View1' AS

    SELECT COUNT(voto_uniforme1) as quantidade, 'uniforme1' as uniforme 
            FROM votos_uniforme WHERE votos_uniforme.voto_uniforme1 = 1
    UNION ALL
    SELECT COUNT(voto_uniforme2) as quantidade, 'uniforme2' as uniforme 
            FROM votos_uniforme WHERE votos_uniforme.voto_uniforme2 = 1
    UNION ALL
    SELECT COUNT(voto_uniforme3) as quantidade, 'uniforme3' as uniforme 
            FROM votos_uniforme WHERE votos_uniforme.voto_uniforme3 = 1

>

SELECT * FROM View1 ORDER BY quantidade DESC