Eduardo e colegas,
Eduardo: Grato pela dica do ASCII. aprendi mais uma.
Desculpem-me pelo e-mail extenso, mas segue aqui o relato em detalhes do
problema que estou enfrentando. A solução deve ser simples, apesar do e-mail
extenso.
Observações:
a.) tentei fazer o ORDER BY sem usar o DENSE_RANK e o resultado é o mesmo,
não funcionou na query original postada abaixo. Estranho que na simulação
funcionou.
b.) Parece que o ORDER BY (desc , asc) não está sendo usado corretamente,
por isso estou postando o problema, que certamente será lessons learn para o
grupo.
c.) Versão do oracle : Oracle 10g.
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
d.) gostaria de resolver isso somente com SQL, sem ter que fazer código em
PL/SQL.
Objetivo:
Obter uma lista de valores com classificação pelo maior valor.
Regra do Negócio:
O maior VALOR_1 será o campeão e o DENSE_RANK dele deve ser 1.
Se empatar no VALOR_1, analisar o maior VALOR_2.
Se ainda assim empatar, o critério de desempate é o menor CODE.
1.) Query montada para simular usando o DENSE_RANK. (AQUI FUNCIONOU)
select id, periodo, valor_1, valor_2, codigo,
DENSE_RANK()
OVER (PARTITION BY periodo
ORDER BY valor_1 desc ,
valor_2 desc ,
codigo asc) AS DENSE_RANK
from (select id,
periodo,
sum(num1) valor_1,
sum(num2) valor_2,
codigo
from(select 1 id, 1 periodo, 100.34 as num1, 200.454545 as num2, '0691' as
codigo from dual
union all
select 2 id, 1 periodo, 100.34 as num1, 200.454545 as num2, '0447' as
codigo from dual
union all
select 3 id, 1 periodo, 30 as num1, 1200 as num2, '0999' as codigo
from dual
union all
select 4 id, 1 periodo, 30 as num1, 1200 as num2, '0001' as codigo
from dual
union all
select 5 id, 1 periodo, 33 as num1, 1200 as num2, '0999' as codigo
from dual
union all
select 6 id, 1 periodo, 30 as num1, 1200 as num2, '0888' as codigo
from dual)
group by id, periodo, codigo)
Resultado:
id per. valor_1 valor_2
code dense_rank
---- ----- ---------------------------
-------------------------- -------- -----------------
2 1 100,34 200,454545 0447
1
1 1 100,34 200,454545 0691
2
5 1 33 1200
0999 3
4 1 30 1200
0001 4
6 1 30 1200
0888 5
3 1 30 1200
0999 6
Conclusão:
O valor 100,34 empatou nas 2 primeiras linhas,
O valor 200,454545 empatou nas 2 primeiras linhas
Preciso que o menor código desempate o ranking.
No caso simulado fez corretamente, pois o 0447 é o menor código das linhas
empatadas e veio com DENSE_RANK = 1.
2.) Query original (AQUI NÃO FUNCIONA).
Datatypes relevantes: A coluna attribute_value é um NUMBER(38,8)
e a coluna CODE é um VARCHAR2(4).
A query abaixo, fiz tambem, sem SUM, trazendo os valores direto em 1
linha só, colocando duas vezes a tabela entidade_summary e o problema
persiste.
SELECT id, periodo, valor_1, valor_2, code,
DENSE_RANK()
OVER (PARTITION BY periodo
ORDER BY valor_1 desc,
valor_2 desc,
code asc) AS DENSE_RANK
FROM (SELECT parent_id id,
si_period_id periodo,
sum(valor_1) valor_1,
sum(valor_2) valor_2,
code
FROM(SELECT d.parent_id,
d.si_period_id,
d.attribute_value valor_1,
null valor_2,
entidade.code
FROM entidade_summary d
, entidade
WHERE d.attribute_code = 'VALOR_1'
AND d.si_period_id = 1
AND d.attribute_value IS NOT NULL
AND d.reference_year IS NULL
AND d.parent_id = entidade.id
UNION ALL
SELECT d.parent_id,
d.si_period_id,
null valor_1,
d.attribute_value valor_2,
entidade.code
FROM entidade_summary d
, entidade
WHERE d.attribute_code = 'VALOR_2'
AND d.si_period_id = 1
AND d.attribute_value IS NOT NULL
AND d.reference_year IS NULL
AND d.parent_id = entidade.id
)
GROUP BY parent_id,
si_period_id,
code
)
Resultado gerado pela query original
:
id per. valor_1 valor_2
code dense_rank
---- ----- ---------------------------
-------------------------- -------- -----------------
7 1 16262927843,1253 1303006,45802409 0688 1
18 1 16262927843,1153 1303006,45802409 0691 2
15 1 16262927843,1153 1303006,45802409 0447 3
1 1 16262927843,1153 1303006,45802409 0687 4
19 1 3930077927,06403 314894,446515 0348 5
Análise:
O valor 16262927843,1253 é o campeão, está com DENSE_RANK = 1. Isto está
correto.
O valor 16262927843,1153 aparece na segunda e terceira linha. Então empatou.
O segundo critério é avaliar VALOR_2. que também empatou na primeira e
segunda linha.
O critério final de desempate é classificar pelo menor CODE.
Como o código 0447 é menor, é esperado que ele apareça no DENSE_RANK = 2 e
não 3.
O que parece não estar funcionando é essa parte da query:
ORDER BY valor_1 desc,
valor_2 desc,
code asc) AS DENSE_RANK
Parece que não está fazendo a ordenação ascendente pela coluna CODE.
Pois, empatou em VALOR_1, empatou em VALOR_2 e o desempate seria pelo menor
código,
no entanto isso não está acontecendo, pois o CODE 0447 veio como 3 e não
como 2.
Resultado pretendido:
id per. valor_1 valor_2
code dense_rank
---- ----- ---------------------------
-------------------------- -------- -----------------
3 15 1 16262927843,1153 1303006,45802409 0447 2
2 18 1 16262927843,1153 1303006,45802409 0691 3
Se alguém souber o motivo pelo qual o DESC, DESC, ASC não acontece, ficarei
grato.
Grato
Eriovaldo
2009/4/23 Claro, Eduardo <eduardo.cl...@eds.com>
>
>
> Gleyson,
>
> A função ASCII retorna o código ASCII do primeiro caractere da sua string.
> Portanto, ASCII('0691') e ASCII('0447') na verdade serão executados como
> simplesmente ASCII('0'), e por isso o resultado é igual.
>
> Quanto à sua query, por favor poste um exemplo completo, que possa ser
> executado e verificado, para facilitar.
>
> Seu exemplo pode por exemplo ser do tipo que você usou no início da sua
> explicação, com dois SELECTs FROM DUAL, mas já usando o DENSE RANK e
> reproduzindo o problema, ok?
>
> --
>
> Eduardo Claro
>
> From: oracle_br@yahoogrupos.com.br <oracle_br%40yahoogrupos.com.br>[mailto:
> oracle_br@yahoogrupos.com.br <oracle_br%40yahoogrupos.com.br>] On Behalf
> Of Eriovaldo Andrietta
> Sent: quinta-feira, 23 de abril de 2009 11:27
> To: oracle_br@yahoogrupos.com.br <oracle_br%40yahoogrupos.com.br>
> Subject: Re: [oracle_br] Dúvida sobre Order by
>
>
> Gleyson,
>
> 1.) Eu já tenho um outro order by, que é usado depois que consigo o
> DENSE_RANK.
> O meu problema é o order by que gera o DENSE_RANK, pois espero que a coluna
> alfanumérica seja classificada de forma ascendente para trazer o código
> 0447 com DENSE_RANK = 1 e não igual a 2. Estou entendendo que o DENSE_RANK
> é
> o rownum do resultado.
>
> 2.) Estou tentando fazer um order by direto na query sem DENSE_RANK e
> também não está funcionando.
>
> ORDER BY d.si_period_id asc, -- 01
> d.attribute_value desc, -- 02
> e.attribute_value desc, -- 03
> entidade.code asc -- 04
> 1 2 3 4
> dense
> 1 16262927843,11529920 1303006,45802409 0691 1 18
> 1 16262927843,11529920 1303006,45802409 0447 2 15
> 1 3930077927,06403017 314894,44651500 0348 3 19
> 1 3744493176,06001997 300054,66430277 0450 4 13
>
> Preciso que o dense da segunda linha seja 1 e o dense da primeira seja 2
> que é a classificação ascendente da quarta coluna, o código menor primeiro.
>
> 3.) Curioso:
>
> se eu fizer :
>
> SQL> select ascii('0691') from dual;
>
> ASCII('0691')
> -------------
> 48
> SQL> select ascii('0447') from dual;
>
> ASCII('0447')
> -------------
> 48
>
> Será que o order by está usando isso para classificar ?
>
> Grato pela ajuda.
>
> 2009/4/23 Gleyson Melo <gleysonm...@gmail.com
> <gleysonmelo%40gmail.com><mailto:
> gleysonmelo%40gmail.com <gleysonmelo%2540gmail.com>> >
>
> >
> >
> > Fala Eriovaldo!
> > Cara, aquele ORDER BY foi apenas pra gerar o valor final do RANK. Pra
> gerar
> > a saída que tu queres, tens que colocar o ORDER BY no final da consulta
> > também.
> >
> > 2009/4/22 Eriovaldo Andrietta
> > <ecandrie...@gmail.com<ecandrietta%40gmail.com><mailto:
> ecandrietta%40gmail.com <ecandrietta%2540gmail.com>> <ecandrietta%
> 40gmail.com>>
>
> >
> >
> >
> > >
> > >
> > > Olá pessoal,
> > >
> > > Veja se alguém pode me ajudar:
> > >
> > > Quando faço esta query ordenando a coluna text de forma desc tenho 0447
> > no
> > > final:
> > > select 100 as num1, 200 as num2,
> > > '0691' as text from dual
> > > union all
> > > select 100 as num1, 200 as num2,
> > > '0447' as text from dual
> > > order by 1 desc, 2 desc, 3 desc
> > >
> > > 1 100 200 0691
> > > 2 100 200 0447
> > >
> > > Quando faço esta query ordenando a coluna text de forma asc tenho 0447
> no
> > > começo:
> > >
> > > select 100 as num1, 200 as num2,
> > > '0691' as text from dual
> > > union all
> > > select 100 as num1, 200 as num2,
> > > '0447' as text from dual
> > > order by 1 desc, 2 desc, 3 asc
> > >
> > > 1 100 200 0447
> > > 2 100 200 0691
> > >
> > > até ai tudo bem. é isso mesmo que eu quero, O desc desc asc funciona,
> > > porém:
> > >
> > > quando uso esse mesmo recurso em:
> > >
> > > DENSE_RANK()
> > > OVER (PARTITION BY d.si_period_id
> > > ORDER BY d.attribute_value desc ,
> > > e.attribute_value desc ,
> > > f.code asc) AS DENSE_RANK
> > >
> > > o 0447 sai sempre no final.
> > >
> > > Eu preciso do 0447 na primeira linha e a instrução não faz isso.
> > >
> > > Sai assim:
> > >
> > > 1 18 1 16262927843,11529920 1303006,45802409 0691 1
> > > 2 15 1 16262927843,11529920 1303006,45802409 0447 2
> > >
> > > E preciso do resultado assim, o menor código antes:
> > >
> > > 1 18 1 16262927843,11529920 1303006,45802409 0447 1
> > > 2 15 1 16262927843,11529920 1303006,45802409 0691 2
> > >
> > > O que tem de errado nisso ?
> > >
> > > Grato
> > > Eriovaldo
> > >
> > > [As partes desta mensagem que não continham texto foram removidas]
> > >
> > >
> > >
> >
> > --
> > Atenciosamente,
> > Gleyson Melo
> > Oracle Database 10g Administrator Certified Professional
> >
> > [As partes desta mensagem que não continham texto foram removidas]
> >
> >
> >
>
> [As partes desta mensagem que não continham texto foram removidas]
>
> [As partes desta mensagem que não continham texto foram removidas]
>
>
>
[As partes desta mensagem que não continham texto foram removidas]
