[Unpivot no Postgres 9.3]

No postgresql, não usa first 10, usa limit 10 no final da consulta.

tente assim:

with unpivot as
(    
    select cl_1 as dezena from concurso_lotofacil
    union all
    select cl_2 from concurso_lotofacil
    union all
    select cl_3 from concurso_lotofacil
    union all
    select cl_4 from concurso_lotofacil
    union all
    select cl_5 from concurso_lotofacil
    union all
    select cl_6 from concurso_lotofacil
    union all
    select cl_7 from concurso_lotofacil
    union all
    select cl_8 from concurso_lotofacil
    union all
    select cl_9 from concurso_lotofacil
    union all
    select cl_10 from concurso_lotofacil
    union all
    select cl_11 from concurso_lotofacil
    union all
    select cl_12 from concurso_lotofacil
    union all
    select cl_13 from concurso_lotofacil
    union all
    select cl_14 from concurso_lotofacil
    union all
    select cl_15 from concurso_lotofacil
)
select 
    Dezena,
    COUNT(*) as Qtd
from unpivot
group by
    Dezena
order by
    Qtd desc
    limit 10

 

Perfeito amigo, vou testar na aplicação, mas deu certo. Obrigado

[Unpivot no Postgres 9.3]

Olá pessoal do Postgres. Tenho esse código abaixo que utilizava no Firebid pra trazer os 10 números mais sorteados e ao utilizar no Postgres não deu certo. Alguém pode ajudar?

 

with unpivot as
(    
    select cl_1 as dezena from concurso_lotofacil
    union all
    select cl_2 from concurso_lotofacil
    union all
    select cl_3 from concurso_lotofacil
    union all
    select cl_4 from concurso_lotofacil
    union all
    select cl_5 from concurso_lotofacil
    union all
    select cl_6 from concurso_lotofacil
    union all
    select cl_7 from concurso_lotofacil
    union all
    select cl_8 from concurso_lotofacil
    union all
    select cl_9 from concurso_lotofacil
    union all
    select cl_10 from concurso_lotofacil
    union all
    select cl_11 from concurso_lotofacil
    union all
    select cl_12 from concurso_lotofacil
    union all
    select cl_13 from concurso_lotofacil
    union all
    select cl_14 from concurso_lotofacil
    union all
    select cl_15 from concurso_lotofacil
)
select first 10
    Dezena,
    COUNT(*) as Qtd
from unpivot
group by
    Dezena
order by
    Qtd desc

[Unpivot no MySql]

Olá galera, beleza?

Bom, quero a ajuda no seguinte item abaixo aonde tento trazer os número mais sorteados (10) e da erro. Pra ser bem sincero eu usava esse código no Firebird e funcionava normalmente, mas como estou me aventurando no MySql gostaria de fazer funcionar esse código.

 

public DataTable getResultadosMaisSorteados_Dez()
        {
            Cmd.CommandText = @"with CTE_Unpivot as
                                (    
                                    select cl_1 as dezena from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_2 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_3 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_4 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_5 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_6 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_7 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_8 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_9 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_10 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_11 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_12 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_13 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_14 from thsoftwares02.concurso_lotofacil
                                    union all
                                    select cl_15 from thsoftwares02.concurso_lotofacil
                                )

                                select first 10
                                    Dezena,
                                    COUNT(*) as Qtd
                                from CTE_Unpivot
                                group by
                                    Dezena
                                order by
                                    Qtd desc";

            DataTable dt = ExecutaSelect();
            return dt;
        }