Delete duplicity

tayna2oo9 · Thu, 17 Apr 2014 8:41 am

Friends, good morning!

I kindly ask orientation to solve this situation that days comes with me sleeping.
I have the following SQL

Select all

select 
 
 T.NR_SEQ_ORDEM_SERV N_ORDEM, 
 S.DT_ORDEM_SERVICO ABERTO, 
 obter_dif_data( S.DT_ORDEM_SERVICO,T.DT_HISTORICO,0) HORA_FECHADA_REAL  ,   
 S.IE_PRIORIDADE 
  
    from MAN_ORDEM_SERVICO S, 
         MAN_ORDEM_SERV_TECNICO T 
     
where 
(T.NR_SEQ_ORDEM_SERV = S.NR_SEQUENCIA) 
AND (S.dt_ordem_servico between :dt_inicial and :dt_final) 
AND (S.IE_PRIORIDADE =:prioridade or S.IE_PRIORIDADE =:prioridade2) -- Prioridede de U e E 
AND (S.NR_GRUPO_PLANEJ=81) 
 
order by 1,2 asc

that returns

n_ordem Open time_fechada_real IE_Priority

Select all

69121	02/01/2014 10:22:51	22:37:08	E 
69121	02/01/2014 10:22:51	00:36:26	E 
69139	02/01/2014 14:06:35	154:53:28	E 
69139	02/01/2014 14:06:35	19:55:56	E 
69168	03/01/2014 08:56:07	136:03:56	E 
69168	03/01/2014 08:56:07	00:44:13	E 
69173	03/01/2014 09:43:57	135:16:06	E 
69173	03/01/2014 09:43:57	00:36:09	E 
69176	03/01/2014 10:40:48	170:46:29	U 
69176	03/01/2014 10:40:48	170:46:39	U 
69176	03/01/2014 10:40:48	76:05:08	U 
69182	03/01/2014 12:54:54	69:28:16	E 
69182	03/01/2014 12:54:54	204:05:15	E 
69182	03/01/2014 12:54:54	69:36:13	E 
69184	03/01/2014 13:20:12	188:53:30	U 
69184	03/01/2014 13:20:12	68:57:26	U 
69184	03/01/2014 13:20:12	188:53:39	U 
69202	03/01/2014 16:22:48	00:07:20	E 
69202	03/01/2014 16:22:48	416:37:19	E 
69224	06/01/2014 08:04:46	136:55:23	E 
69224	06/01/2014 08:04:46	04:41:14	E 
69233	06/01/2014 09:28:27	135:31:42	E 
69233	06/01/2014 09:28:27	00:37:39	E 
69325	08/01/2014 08:35:25	23:54:27	U 
69325	08/01/2014 08:35:25	33:03:03	U 
69325	08/01/2014 08:35:25	33:03:08	U 
69326	08/01/2014 08:36:30	52:47:21	U 
69326	08/01/2014 08:36:30	52:47:12	U 
69326	08/01/2014 08:36:30	00:40:00	U 
69431	10/01/2014 09:42:16	264:06:41	E 
69431	10/01/2014 09:42:16	288:28:42	E 
69431	10/01/2014 09:42:16	266:54:28	E 
69431	10/01/2014 09:42:16	241:05:32	E 
69431	10/01/2014 09:42:16	174:49:10	E 
69431	10/01/2014 09:42:16	122:44:50	E 
69431	10/01/2014 09:42:16	127:00:48	E 
69431	10/01/2014 09:42:16	124:42:30	E 
69431	10/01/2014 09:42:16	172:54:40	E 
69431	10/01/2014 09:42:16	172:54:32	E 
69431	10/01/2014 09:42:16	143:50:40	E 
69431	10/01/2014 09:42:16	121:27:28	E 
69455	10/01/2014 15:33:43	297:26:26	E 
69455	10/01/2014 15:33:43	160:50:47	E 
69553	14/01/2014 11:37:34	373:22:33	U 
69553	14/01/2014 11:37:34	03:54:51	U

I kindly ask for guidance to remove n_ordem duplicates but with higher hours, example:

Select all

69121	02/01/2014 10:22:51	22:37:08	E 
69121	02/01/2014 10:22:51	00:36:26	E

SQL should only display

Select all

69121	02/01/2014 10:22:51	00:36:26	E

, because it contains fewer hours.

From now I thank everyone's attention, thank you.

Thu, 17 Apr 2014 10:34 am

Sister Tayna, use the distinct or unique to avoid duplicity:

Select all

 
    select  distinct 
 
    T.NR_SEQ_ORDEM_SERV N_ORDEM, 
    S.DT_ORDEM_SERVICO ABERTO, 
    obter_dif_data( S.DT_ORDEM_SERVICO,T.DT_HISTORICO,0) HORA_FECHADA_REAL  ,  
    S.IE_PRIORIDADE 
 
        from MAN_ORDEM_SERVICO S, 
             MAN_ORDEM_SERV_TECNICO T 
        
    where 
    (T.NR_SEQ_ORDEM_SERV = S.NR_SEQUENCIA) 
    AND (S.dt_ordem_servico between :dt_inicial and :dt_final) 
    AND (S.IE_PRIORIDADE =:prioridade or S.IE_PRIORIDADE =:prioridade2) -- Prioridede de U e E 
    AND (S.NR_GRUPO_PLANEJ=81) 
 
    order by 1,2 asc

tayna2oo9 · Thu, 17 Apr 2014 11:08 am

Adriano, good morning!

Thanks for the attention, but his suggestion did not have the expected effect, it repairs that the time is different, and I want to display only the smaller hours, discard the largest.

Thu, 17 Apr 2014 12:12 pm

Tayna, does a grouping using Group by

tayna2oo9 · Thu, 17 Apr 2014 3:30 pm

Good afternoon, I realized Group by .. I did not get success.

tayna2oo9 · Thu, 24 Apr 2014 2:15 pm

Good afternoon, in an attempt to solve my situation I tried to make a subselect, runs normally however it is not respecting the condition, that is still contain to bring undue information. Someone can guide me, thank you

Select all

SELECT  
        A.NR_SEQUENCIA, 
        B.DT_ATUALIZACAO, 
        obter_dif_data( A.DT_ORDEM_SERVICO,B.DT_HISTORICO,0) HORA_FECHADA_REAL, 
        A.IE_PRIORIDADE, 
        A.NR_GRUPO_PLANEJ 
         
       FROM MAN_ORDEM_SERVICO A, 
            MAN_ORDEM_SERV_TECNICO B 
        
       WHERE 
A.NR_SEQUENCIA = B.NR_SEQ_ORDEM_SERV 
AND(A.NR_GRUPO_PLANEJ =81) 
AND(A.IE_PRIORIDADE <> 'B')   
AND(A.IE_STATUS_ORDEM = 2) 
AND( A.DT_ORDEM_SERVICO BETWEEN :dt_inicial and :dt_final) 
[b]AND (B.DT_ATUALIZACAO < (SELECT C.DT_ATUALIZACAO FROM  MAN_ORDEM_SERV_TECNICO C WHERE ROWNUM = 1 AND (C.NR_SEQ_ORDEM_SERV = B.NR_SEQ_ORDEM_SERV)))[/b] 
 
ORDER BY 1,3 ASC

tora34 · Fri, 25 Apr 2014 11:28 am

Good morning Tanya,
I'm trying to understand your doubt and the query sent, what does the function get_dif_data do?
Could you try to explain the real need of this query? What kind of result do you intend to present to the end user?

gesiel · Wed, 16 Jul 2014 1:45 pm

Tayna,

I believe that it has already solved, but it goes a suggestion:

Select all

SELECT  
 ORDEM, 
 ABERTO, 
 MIN(HORA_FECHADA_REAL) HORA_FECHADA, 
 PRIORIDADE 
 FROM 
 ( 
 select  distinct 
 
    T.NR_SEQ_ORDEM_SERV N_ORDEM, 
    S.DT_ORDEM_SERVICO ABERTO, 
    obter_dif_data( S.DT_ORDEM_SERVICO,T.DT_HISTORICO,0) HORA_FECHADA_REAL  ,  
    S.IE_PRIORIDADE PRIORIDADE 
 
        from MAN_ORDEM_SERVICO S, 
             MAN_ORDEM_SERV_TECNICO T 
        
    where 
    (T.NR_SEQ_ORDEM_SERV = S.NR_SEQUENCIA) 
    AND (S.dt_ordem_servico between :dt_inicial and :dt_final) 
    AND (S.IE_PRIORIDADE =:prioridade or S.IE_PRIORIDADE =:prioridade2) -- Prioridede de U e E 
    AND (S.NR_GRUPO_PLANEJ=81) 
) X 
GROUP BY ORDEM, ABERTO, PRIORIDADE 
    order by 1,2 asc;

Good Luck

Thu, 17 Jul 2014 9:18 am

Hello Tayna2OO9,

Another way to resolve this issue could be the use of dense_rank function. This function allows you to classify the quieous results according to a criterion by assigning a note (or rank) to the return result.

For a better understanding, perform the steps below:
(a) Try to assign a classification to the quire results. To do this, use dense_rank, grouping the results by t.nr_seq_ordem_serv,s.dt_ordem_servico,s.ie_prioridade and then ordering them by obter_dif_data(s.dt_ordem_servico, t.dt_historico, 0) the queire below (which eventually need to be revised) allows you to view the data:

2]]

You will notice that the last column rank displays values ??like 1,2,3. When the craving has a value greater than 1, then duplicate records are dealt with.

B) The next step is to filter in the queries the records that interest you. In this case, we must select the records with rank = 1, which means that for a determine t.nr_seq_ordem_serv,s.dt_ordem_servico,s.ie_prioridade this would be the difference of lower value.
then follow the desired queries:

Select all

SELECT n_ordem, 
       aberto, 
       hora_fechada_real, 
       ie_prioridade 
  FROM (SELECT t.nr_seq_ordem_serv n_ordem, 
               s.dt_ordem_servico aberto, 
               obter_dif_data(s.dt_ordem_servico, t.dt_historico, 0) hora_fechada_real, 
               s.ie_prioridade, 
               DENSE_RANK() OVER (PARTITION BY t.nr_seq_ordem_serv,s.dt_ordem_servico,s.ie_prioridade ORDER BY obter_dif_data(s.dt_ordem_servico, t.dt_historico, 0) ASC) AS RANK 
          FROM man_ordem_servico s,  
               man_ordem_serv_tecnico t 
         WHERE (t.nr_seq_ordem_serv = s.nr_sequencia) 
           AND (s.dt_ordem_servico BETWEEN :dt_inicial and :dt_final) 
           AND (s.ie_prioridade = :prioridade OR s.ie_prioridade = :prioridade2)  
           AND (s.nr_grupo_planej = 81) 
 WHERE rank = 1            
 ORDER BY 1, 2 ASC

For a better understanding / learning, at this Oracle-Base link You find an excellent article that Explain this function in more detail: http://www.oracle-base.com/articles/mis ... ctions.php
Good luck,

Sergio Coutinho

Delete duplicity

Who is online