order by in oracle subquerys

Question

order by in oracle subquerys

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

RESOLVED

I decided to create a view equal to my work order table plus it was ordered by the team and the priority, and I kept the above query by changing only the table of the subquery that I placed the view I created, so I do not have to worry about the ordering because the oracle will bring the way the view brings and the view already brings orderly so it gave right, at least for now ... thank you very much for all who tried to help and if by chance they know in a better way to solve my problem, do not stop answering me: D

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

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

A table with work orders, let's say it has only 5 fields that are: ORD_ID | TEAM_MANUT | DATA_BOOT | PRIORI | OBSERVATION.

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

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 was able to list only 5 O.S of each TEAM_MANUT the problem starts when I try to return the 5 with the lower PRIORI value, I will show the query that 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 it after the already ordered data, so I can not connect C1.EQUIPE_MANUT with C2.EQUIPE_MANUT I think it should be a subquery already inside another subquery ... well this is if someone can help me: D

oracle query pl-sql order

asked by anonymous 23.08.2017 / 15:04

2 answers

0

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 to do sorting.

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

23.08.2017 / 15:19

Error 403 disallowed_useragent, when logging with google in mobile browser using angularfire2 ConvertNumber of JSF negative values

score 0 · Accepted Answer

I decided to create a view equal to my work order table plus it was ordered by the team and the priority, and I kept the above query by changing only the table of the subquery that I placed the view I created, so I do not have to worry about the ordering because the oracle will bring the way the view brings and the view already brings orderly so it gave right, at least for now ... thank you very much for all who tried to help and if by chance they know in a better way to solve my problem, do not stop answering me: D