[Resolvido] consulta (SQL)

[andrefe 0]

Bom dia,

 

Preciso de uma ajuda com uma consulta em SQL (Oracle),

o que preciso é apenas a primeira linha para cada numorp que repetir

 

imagem:

 

o resultado que necessito é:

 

numorp | seqrot | qtdre1 ...

3330 | 60 | 0

3432 | 160 | 0

3434 | 160 | 600

3436 | 160 | 0

3438 | 160 | 0

3440 | 160 | 0

4371 | 100 | 0

...

 

só a primeira linha quando numorp repetir

tentei usar o rownum mas não deu certo

 

Se alguém puder ajudar, agradeço

 

esse é ó código da consulta:

 

select e900oop.numorp,E900OOP.SEQROT,E900OOP.QTDRE1,E900OOP.TMPPRP,E900OOP.TMPTPR,E900EOQ.CODETG,
E900OOP.CODEMP,E900OOP.CODORI,E900OOP.NUMORP,E900OOP.CODOPR,E900OOP.CODCRE,E900OOP.DTRFIM,E900OOP.DTPINI,
E900OOP.USU_TURTRB,E900OOP.NUMPRI,E900OOP.OBSOOP,E900OOP.QTDPRV

FROM E900OOP,E900COP,E725CRE,E720OPR,E900EOQ,USU_TSETOR where

E900COP.CODEMP=E900OOP.CODEMP and
E900COP.CODORI=E900OOP.CODORI and
E900COP.NUMORP=E900OOP.NUMORP and
E900OOP.CODEMP=E720OPR.CODEMP and
E900OOP.CODOPR=E720OPR.CODOPR and
E900OOP.CODEMP=E725CRE.CODEMP and
E900OOP.CODCRE=E725CRE.CODCRE and
E720OPR.USU_CODSET=USU_TSETOR.USU_CODSET and
E900EOQ.NUMORP = E900OOP.NUMORP AND
E900EOQ.CODEMP = E900OOP.CODEMP AND
E900EOQ.CODETG = E900OOP.CODETG AND
E900OOP.CODORI = 'CUR' and E900COP.SITORP = 'A'
and e900oop.dtrfim = '31/12/1900' and
e900eoq.seqeoq = '2'
order by e900oop.numorp,e900oop.seqrot

[macielcr7 9]

group by

select * from tabela group by nome

ou

 

distinct

select distinct nome from tabela

[andrefe 0]

group by

select * from tabela group by nome

ou

 

distinct

select distinct nome from tabela

 

Olá, já tentei dessa forma, ele não agrupa corretamente :(

[Motta 645]

Tente com um MIN

 

select e900oop.numorp,E900OOP.SEQROT,E900OOP.QTDRE1,E900OOP.TMPPRP,E900OOP.TMPTPR,E900EOQ.CODETG,
E900OOP.CODEMP,E900OOP.CODORI,E900OOP.NUMORP,E900OOP.CODOPR,E900OOP.CODCRE,E900OOP.DTRFIM,E900OOP.DTPINI,
E900OOP.USU_TURTRB,E900OOP.NUMPRI,E900OOP.OBSOOP,E900OOP.QTDPRV

FROM E900OOP,E900COP,E725CRE,E720OPR,E900EOQ,USU_TSETOR where

E900COP.CODEMP=E900OOP.CODEMP and
E900COP.CODORI=E900OOP.CODORI and
E900COP.NUMORP=E900OOP.NUMORP and
E900OOP.CODEMP=E720OPR.CODEMP and
E900OOP.CODOPR=E720OPR.CODOPR and
E900OOP.CODEMP=E725CRE.CODEMP and
E900OOP.CODCRE=E725CRE.CODCRE and
E720OPR.USU_CODSET=USU_TSETOR.USU_CODSET and
E900EOQ.NUMORP = E900OOP.NUMORP AND
E900EOQ.CODEMP = E900OOP.CODEMP AND
E900EOQ.CODETG = E900OOP.CODETG AND
E900OOP.CODORI = 'CUR' and E900COP.SITORP = 'A'
and e900oop.dtrfim = '31/12/1900' and
e900eoq.seqeoq = '2'
and E900OOP.SEQROT = (select min(E900OOP2.SEQROT from E900COP E900COP2 where e900oop.numorp = e900oop2.numorp)
order by e900oop.numorp,e900oop.seqrot

 

Vai listar apenas o menor dos "E900OOP2.SEQROT".

[andrefe 0]

Obrigado Motta, de uma certa maneira deu certo

 

se eu trocar

and e900oop.dtrfim = '31/12/1900' and

por

and e900oop.dtrfim <> '31/12/1900' and

 

ele lista tudo da maneira que preciso, mas com as informações erradas

preciso de tudo que dtrfim = '31/12/1900'

não entendi por que não funciona quando está =, não retorna nada

 

select e900oop.numorp,E900OOP.SEQROT,E900OOP.QTDRE1,E900OOP.TMPPRP,E900OOP.TMPTPR,E900EOQ.CODETG,
E900OOP.CODEMP,E900OOP.CODORI,E900OOP.NUMORP,E900OOP.CODOPR,E900OOP.CODCRE,E900OOP.DTRFIM,E900OOP.DTPINI,
E900OOP.USU_TURTRB,E900OOP.NUMPRI,E900OOP.OBSOOP,E900OOP.QTDPRV

FROM E900OOP,E900COP,E725CRE,E720OPR,E900EOQ,USU_TSETOR where

E900COP.CODEMP=E900OOP.CODEMP and
E900COP.CODORI=E900OOP.CODORI and
E900COP.NUMORP=E900OOP.NUMORP and
E900OOP.CODEMP=E720OPR.CODEMP and
E900OOP.CODOPR=E720OPR.CODOPR and
E900OOP.CODEMP=E725CRE.CODEMP and
E900OOP.CODCRE=E725CRE.CODCRE and
E720OPR.USU_CODSET=USU_TSETOR.USU_CODSET and
E900EOQ.NUMORP = E900OOP.NUMORP AND
E900EOQ.CODEMP = E900OOP.CODEMP AND
E900EOQ.CODETG = E900OOP.CODETG AND
E900OOP.CODORI = 'CUR' and E900COP.SITORP = 'A'
and e900oop.dtrfim = '31/12/1900' and
e900eoq.seqeoq = '2'
and E900OOP.SEQROT = (select min(E900OOP.SEQROT) from E900OOP where e900oop.numorp = e900oop.numorp)
order by e900oop.numorp,e900oop.seqrot

[Motta 645]

Repita as condições no sub-select.

 

select e900oop.numorp,E900OOP.SEQROT,E900OOP.QTDRE1,E900OOP.TMPPRP,E900OOP.TMPTPR,E900EOQ.CODETG,
E900OOP.CODEMP,E900OOP.CODORI,E900OOP.NUMORP,E900OOP.CODOPR,E900OOP.CODCRE,E900OOP.DTRFIM,E900OOP.DTPINI,
E900OOP.USU_TURTRB,E900OOP.NUMPRI,E900OOP.OBSOOP,E900OOP.QTDPRV

FROM E900OOP,E900COP,E725CRE,E720OPR,E900EOQ,USU_TSETOR where

E900COP.CODEMP=E900OOP.CODEMP and
E900COP.CODORI=E900OOP.CODORI and
E900COP.NUMORP=E900OOP.NUMORP and
E900OOP.CODEMP=E720OPR.CODEMP and
E900OOP.CODOPR=E720OPR.CODOPR and
E900OOP.CODEMP=E725CRE.CODEMP and
E900OOP.CODCRE=E725CRE.CODCRE and
E720OPR.USU_CODSET=USU_TSETOR.USU_CODSET and
E900EOQ.NUMORP = E900OOP.NUMORP AND
E900EOQ.CODEMP = E900OOP.CODEMP AND
E900EOQ.CODETG = E900OOP.CODETG AND
E900OOP.CODORI = 'CUR' and E900COP.SITORP = 'A'
and e900oop.dtrfim = '31/12/1900' and
e900eoq.seqeoq = '2'
and E900OOP.SEQROT = (select min(E900OOP.SEQROT) 
                     from E900OOP E900OOP2
                     where E900OOP2.numorp = e900oop.numorp
                     and E900OOP2.CODORI = 'CUR' 
                     and E900COP2.SITORP = 'A'
                     and e900oop2.dtrfim = '31/12/1900' 
                     and e900eoq2.seqeoq = '2')
order by e900oop.numorp,e900oop.seqrot

[andrefe 0]

Ok, mas o problema persiste

 

será que o problema não está nesse min?

pq se eu colocar max ele acha resultados, mas o min deve estar pegando somente seqrot quando seu valor for 1 (dtrfim <> 31/12/1900), ele não encontra nada pq quando min for = 1 dtrfim vai ser diferente de '31/12/1900' e já o max quando seqrot tiver valor maximo tem casos que dtrfim = '31/12/1900'

 

Pode ser que não seja isso, mas tem um pouco de lógica

[Motta 645]

Como foi dito se quer apenas um registro de cada NUMORP uma forma de fazer isto é pagar o mínimo (ou máximo) SEQROT de cada registro.

 

Para isto se faz umb sub-select, as condições do sub-select devem ser as mesmas do select principal.

 

Um exemplo mais simples, último post de Oracle de um user

 

posts
-----
user
num_post
forum
text

 

select p1.user , p1.num_post , p1.text
from   posts p1
where  forum = 'oracle'
and    num_post = (select max(p2.num_post) 
                  from posts p2
                  where p2.user = p1.user
                  and   p2.forum = 'oracle') 

 

Ajudou ?

[andrefe 0]

Motta, muito obrigado, deu certo

faltava apenas colocar mais uma condição no subselect (E900COP.NUMORP=E900OOP.NUMORP)

 

ficou assim:

 

select e900oop.numorp,E900OOP.SEQROT,E900OOP.QTDRE1,E900OOP.TMPPRP,E900OOP.TMPTPR,E900EOQ.CODETG,
E900OOP.CODEMP,E900OOP.CODORI,E900OOP.NUMORP,E900OOP.CODOPR,E900OOP.CODCRE,E900OOP.DTRFIM,E900OOP.DTPINI,
E900OOP.USU_TURTRB,E900OOP.NUMPRI,E900OOP.OBSOOP,E900OOP.QTDPRV

FROM E900OOP,E900COP,E725CRE,E720OPR,E900EOQ,USU_TSETOR where

E900COP.CODEMP=E900OOP.CODEMP and
E900COP.CODORI=E900OOP.CODORI and
E900COP.NUMORP=E900OOP.NUMORP and
E900OOP.CODEMP=E720OPR.CODEMP and
E900OOP.CODOPR=E720OPR.CODOPR and
E900OOP.CODEMP=E725CRE.CODEMP and
E900OOP.CODCRE=E725CRE.CODCRE and
E720OPR.USU_CODSET=USU_TSETOR.USU_CODSET and
E900EOQ.NUMORP = E900OOP.NUMORP AND
E900EOQ.CODEMP = E900OOP.CODEMP AND
E900EOQ.CODETG = E900OOP.CODETG AND
E900OOP.CODORI = 'CUR' and E900COP.SITORP = 'A'
and e900oop.dtrfim = '31/12/1900' and
e900eoq.seqeoq = '2'
and E900OOP.SEQROT = (select min(E900OOP.SEQROT)
                     from E900OOP where
                     E900COP.NUMORP=E900OOP.NUMORP and
                     E900OOP.CODORI = 'CUR' and E900COP.SITORP = 'A'
                     and e900oop.dtrfim = '31/12/1900'
                     and e900eoq.seqeoq = '2')
order by e900oop.numorp,e900oop.seqrot

 

Obrigado pela ajuda!