Ordenar registros e numerar ORDEM DO ITENS

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
carlynhos77
Rank: Analista Sênior
Rank: Analista Sênior
Mensagens: 172
Registrado em: Seg, 24 Out 2016 7:20 pm

Ola, boa tarde

Estou com uma situação q ainda não consegui resolver, tenho duas ideias, qualquer uma q vier primeiro, resolve pra mim... Os registros são itens de licitação, os proponentes dão seus lances pra cada item, apos vejo o item vencedor

tenho uma tabela com os seguintes registros (fictícios):

CODIGO PRODUTO VALOR MELHOR_ OFERTA ORDEM_VENCEDOR
...1.......arroz........10,00...............x.....................1
...1.......arroz........ 13,00......................................2
...1.......arroz........15,50......................................3

...2.......feijao........11,00...............x..........................1
...2.......feijao........12,00...........................................2
...2.......feijao........13,00...........................................3

...3.......sal............2,00...............x.....................1
...3.......sal............3,00......................................2
...3.......sal............4,00......................................3

Podem perceber, que se eu conseguir fazer uma das duas opções em NEGRITO, já resolve pra mim, ou seja, marcar a MELHOR_OFERTA ou gravar a ORDEM_VENCEDOR...

Porem, não to conseguindo classificar e salvar a marcação.. classificar até consegui, porém salvar não...

Preciso ordenar, primeiro pegar o grupo com o mesmo CÓDIGO e depois ordenar pelo VALOR

Alguém pode me ajudar?
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

Olá, estou de saída do trabalho, mas com o uso de funções analíticas é possível sim fazer isso.
Dá uma olhada, se for urgente , senão posso te ajudar na segunda.
Existe função RANK, ou talvez mesmo row_number, particionando pelo grupo que deseja que você consegue esse resultado.
carlynhos77
Rank: Analista Sênior
Rank: Analista Sênior
Mensagens: 172
Registrado em: Seg, 24 Out 2016 7:20 pm

Ola, obrigado pela atenção... eu ate fiz uns teste com o RANK, porém, consegui fazer a classificação mas não consegui salvar eles.. ou seja, não consegui casar o SELECT com o UPDATE usando RANK

Se puder me ajudar, lhe aguardo... muito obrigado
carlynhos77
Rank: Analista Sênior
Rank: Analista Sênior
Mensagens: 172
Registrado em: Seg, 24 Out 2016 7:20 pm

ola, o mais proximo que cheguei ate agora foi disso aqui:

Selecionar tudo

UPDATE LIC_LICITACAO_ITENS t1
   SET ORDEM_VENCEDOR = (SELECT rnk
                        FROM (SELECT VALOR, CODIGO ,
                                     row_number() over (partition by CODIGO 
                                                            order by VALOR) rnk
                                FROM LIC_LICITACAO_ITENS ) t2
                       WHERE t2.CODIGO = t1.CODIGO)

Resultado:
- Para os itens com o valor "0,00", ele jogou o nr 1 pra todos
- Para os demais itens com valor, ele jogou o nr 2 pra todos

Ele não considerou o grupo para gerar a ordem
carlynhos77
Rank: Analista Sênior
Rank: Analista Sênior
Mensagens: 172
Registrado em: Seg, 24 Out 2016 7:20 pm

ola, outro codigo que chegui, diz que foi processada, porém, não dá nenhum resultado

Selecionar tudo

MERGE
INTO    LIC_LICITACAO_ITENS  u
USING   (
SELECT  rowid AS rid,
ROW_NUMBER() OVER (PARTITION BY CODIGO ORDER BY VALOR) AS rn
FROM LIC_LICITACAO_ITENS  
)
ON      (u.rowid = rid)
WHEN MATCHED THEN
UPDATE
SET     u.ORDEM_VENCEDOR = rn
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

Olá,
como não tenho tua tabela e etc fiz algumas suposições mas que creio resolverem teu problema.
A ordenação é até fácil, o problema é quando tiverem 2 primeiros lugares. No meu exemplo priorizei pela ordem do "Fornecedor". Na sua não sei.
Ou se serão eleitos os 2 fornecedores com menor preço (o que daria para usar a função RANK comentada).
O select abaixo mostra apenas os premiados.
Não sei se você precisa atualizar a ordenação de todas licitações. Mas na prática não fará muita diferença, só retirar a condição de ser o primeiro.

Selecionar tudo

WITH LICITACAO AS (
SELECT 1 COD_ITEM , 15 preço, 'A' FORNECEDOR FROM DUAL UNION ALL
SELECT 1 COD_ITEM , 13 preço, 'B' FORNECEDOR FROM DUAL UNION ALL
SELECT 1 COD_ITEM , 10 preço, 'C' FORNECEDOR FROM DUAL UNION ALL
SELECT 1 COD_ITEM , 13 preço, 'D' FORNECEDOR FROM DUAL UNION ALL
SELECT 2 COD_ITEM , 13 preço, 'A' FORNECEDOR FROM DUAL UNION ALL
SELECT 2 COD_ITEM , 12 preço, 'B' FORNECEDOR FROM DUAL UNION ALL
SELECT 2 COD_ITEM , 11 preço, 'C' FORNECEDOR FROM DUAL UNION ALL
SELECT 2 COD_ITEM , 11 preço, 'D' FORNECEDOR FROM DUAL UNION ALL
SELECT 3 COD_ITEM , 3  preço, 'A' FORNECEDOR FROM DUAL UNION ALL
SELECT 3 COD_ITEM , 2  preço, 'B' FORNECEDOR FROM DUAL UNION ALL
SELECT 3 COD_ITEM , 4  preço, 'C' FORNECEDOR FROM DUAL
)
, PREMIADO AS (
SELECT L.*
     , ROW_NUMBER() OVER (PARTITION BY L.COD_ITEM ORDER BY L.preço, L.FORNECEDOR) ORDEM
     /*, RANK() OVER (PARTITION BY P.COD_ITEM ORDER BY P.preço) ORDEM_RANK*/
  FROM LICITACAO L
)
SELECT * 
  FROM PREMIADO P
 WHERE P.ORDEM = 1;
;
Essa parte creio que você fez. Agora para atualizar a tabela utilizando a consulta baseada na de cima , seria assim:

Selecionar tudo

MERGE INTO LICITACAO L
USING (
     SELECT *
       FROM (SELECT LIC.*
                           , ROW_NUMBER() OVER (PARTITION BY L.COD_ITEM ORDER BY L.preço, L.FORNECEDOR) ORDEM
                   FROM LICITACAO LIC
             ) O
      WHERE O.ORDEM = 1
      ) PREMIO
   ON (L.COD_ITEM   = PREMIO.COD_ITEM
   AND L.preço      = PREMIO.preço
   AND L.FORNECEDOR = PREMIO.FORNECEDOR   
     )
WHEN MATCHED THEN 
   UPDATE SET L.ORDEM = PREMIO.ORDEM;
carlynhos77
Rank: Analista Sênior
Rank: Analista Sênior
Mensagens: 172
Registrado em: Seg, 24 Out 2016 7:20 pm

ola, boa noite...obrigado daniel pela ajuda

adaptei o codigo aqui, conforme minha tabela, mas ta dando erro

Selecionar tudo

MERGE INTO LIC_LICITACAO_ITENS L
USING (
     SELECT *
       FROM (SELECT LIC.*,
                           ROW_NUMBER() OVER (PARTITION BY L.CODIGO_ITEM ORDER BY L.VALOR_OFERTADO, L.CODIGO_FORNECEDOR) ORDEM
                   FROM LIC_LICITACAO_ITENS LIC
             ) O
      WHERE O.ORDEM = 1
      ) PREMIO
   ON (L.CODIGO_ITEM   = PREMIO.CODIGO_ITEM
   AND L.VALOR_OFERTADO      = PREMIO.VALOR_OFERTADO
   AND L.CODIGO_FORNECEDOR = PREMIO.CODIGO_FORNECEDOR   
     )
WHEN MATCHED THEN 
   UPDATE SET L.ORDEM_VENCEDOR = PREMIO.ORDEM;
Nessa parte do Partition "L.CODIGO_ITEM ORDER BY L.VALOR_OFERTADO, L.CODIGO_FORNECEDOR", fala q o identificador é invalido
Anexos
tela com a mensagem do erro
tela com a mensagem do erro
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

Olá,

você está referenciando pelo alias errado.
Nessa parte não deve ser "L" e sim "LIC", pois é para particionar segunda tua subquery de filtro.
então ficaria

ROW_NUMBER() OVER (PARTITION BY LIC.CODIGO_ITEM ORDER BY LIC.VALOR_OFERTADO, LIC.CODIGO_FORNECEDOR) ORDEM
carlynhos77
Rank: Analista Sênior
Rank: Analista Sênior
Mensagens: 172
Registrado em: Seg, 24 Out 2016 7:20 pm

Ola, boa tarde... obrigado Daniel

Realmente esse detalhe fez a diferença, corrigi aqui e funcionou perfeitamente... muito obrigado :lol:
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 13 visitantes