postgres Unpivot no Postgres 9.3

[thtrassi]

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

[Dr. House]

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

 

[thtrassi]

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

[Dr. House]

Em 23/09/2015 at 10:48, thtrassi disse:

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

beleza,