order by in oracle subquerys

Question

RESOLVED

I solved as follows, I created a view equal to my table of service orders more that was ordered by the team and by the priority, and then I kept the query above by only changing the table of the subquery that I put the view that I created, so I don’t have to worry about the ordering because the oracle will bring the way the view brings and the view already brings ordered so gave straight, at least for now... thank you so much for everyone who tried to help and if by chance you know a better way to solve my problem do not fail to answer me :D

########################################################################

I found some questions around here similar to my most none of the answers helped me, I have the following situation:

A table with orders of service, imagine that it has only 5 fields that are: ORDEM_ID | EQUIPE_MANUT | DATA_ABERTURA | PRIORI | OBSERVATION.

And I need to set up a web panel that will display the 5 O.S with each team’s lowest PRIORI value.

ex:

ORDEM_ID | EQUIPE_MANUT | DATA_ABERTURA | PRIORI | OBSERVACAO
  0001        100           01/01/2017      1          aaaa
  0002        100           01/01/2017      2          aaaa
  0003        100           01/01/2017      3          aaaa
  0004        100           01/01/2017      4          aaaa
  0005        100           01/01/2017      5          aaaa
  0006        105           01/01/2017      1          aaaa
  0007        105           01/01/2017      2          aaaa
  0008        105           01/01/2017      3          aaaa
  0009        105           01/01/2017      4          aaaa
  0010        105           01/01/2017      5          aaaa

Well I managed to list only 5 O.S of each EQUIPE_MANUT the problem starts when I try to return the 5 with the lowest PRIORI value, I will show the query I have used:

SELECT ORDEM_ID   AS ORDEM,
  DATA_ABERTURA   AS DATA_ABERTURA,
  C1.EQUIPE_MANUT AS EQUIPE,
  TRANSLATE(C1.OBSERVACAO,'âàãáÁÂÀÃéêÉÊíÍóôõÓÔÕüúÜÚÇç','AAAAAAAAEEEEIIOOOOOOUUUUCC') AS OBSERVACAO
FROM CORDEMSERV C1
WHERE C1.ORDEM_ID IN
  (SELECT C2.ORDEM_ID
    FROM CORDEMSERV C2
    WHERE C2.EQUIPE_MANUT = C1.EQUIPE_MANUT
    AND C2.STATUS         ='1'
    AND C2.PRIO          IS NOT NULL
    AND ROWNUM <=5 
  )
AND C1.STATUS='1'
AND C1.PRIO IS NOT NULL
ORDER BY C1.EQUIPE_MANUT;

I have tried to put this subquery inside another subquery without rownum and use itlo after the most ordered data ai i can not connect C1.EQUIPE_MANUT with C2.EQUIPE_MANUT I think that due to be a subquery that already inside another subquery ... good is that if someone can help me :D

Answer 1

So for what you need it works:

with ordens_result as (select Ordem_Id, Equipe_Manut from Cordemserv where Status = '1' and Prio is not null order by Equipe_Manut, Prio)
select C1.Ordem_Id as Ordem
      ,C1.Data_Abertura as Data_Abertura
      ,C1.Equipe_Manut as Equipe
      ,Translate(C1.Observacao, 'âàãáÁÂÀÃéêÉÊíÍóôõÓÔÕüúÜÚÇç', 'AAAAAAAAEEEEIIOOOOOOUUUUCC') as Observacao
  from Cordemserv C1
 where C1.Ordem_Id in (select Ordem_Id from ordens_result
                        where Equipe_Manut = C1.Equipe_Manut
                          and Rownum <= 5)
   and C1.Status = '1'
   and C1.Prio is not null
 order by C1.Equipe_Manut
         ,C1.Prio;

Update: used the With feature for ordering.

(old)Applied analytical function, where I get the max id partitioned by the team and ordered by the team priority.

Answer 2

I solved as follows, I created a view equal to my table of service orders more that was ordered by the team and by the priority, and then I kept the query above by only changing the table of the subquery that I put the view that I created, so I don’t have to worry about the ordering because the oracle will bring the way the view brings and the view already brings ordered so gave straight, at least for now... thank you so much for everyone who tried to help and if by chance you know a better way to solve my problem do not fail to answer me :D