Forum Stats

  • 3,757,562 Users
  • 2,251,245 Discussions
  • 7,869,866 Comments

Discussions

Select Query in Want unique time.

718557
718557 Member Posts: 54
edited Apr 28, 2010 7:47AM in SQL & PL/SQL
Please go through below detail

I have one table.

File Id, Date, Time

00001 10/12/2010 10:10
00001 10/12/2010 10:10
00001 10/12/2010 10:10
00001 10/12/2010 10:11

00002 10/12/2010 10:10
00002 10/12/2010 10:10

I want unique fileid,date and time in select query only.

If apply group by File Id, Date, Time then I want increment time +1.

New column
----------
00001 10/12/2010 10:10 10:10
00001 10/12/2010 10:10 10:12 <-- We can not set 10:11 due to it's already exist in table.
00001 10/12/2010 10:10 10:13
00001 10/12/2010 10:11 10:11


In short i would like select fileid,date,time,(Id duplicate entry then time +1 to generate uniquness).



-------------------------------------
I have do this for myself but i face this problem.


Query to fatch below all duplicate row

time+1
00001 10/12/2010 10:10 10:10
00001 10/12/2010 10:10 10:11 <-- But this enty again become duplicate due to it's already exist in table.
00001 10/12/2010 10:10 10:12

Thanks in advance.

Best Answer

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Accepted Answer
    Hehe there is more simple one :-)
    alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';
    
    with t as(
    select '00001' id,to_date('20101012 10:10','yyyymmdd hh24:mi') val from dual union all
    select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00001',   to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
    select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
    select ID,Val
      from t
     model
    partition by(ID)
    dimension by(row_number() over(partition by id order by Val) as rn)
    measures(Val)
    rules(Val[rn > 1] order by rn
        = greatest(Val[cv()-1]+interVal '1' minute,
                   Val[cv()]));
    
    ID     VAL
    -----  ----------------
    00001  2010/10/12 10:10
    00001  2010/10/12 10:11
    00001  2010/10/12 10:12
    00001  2010/10/12 10:13
    00002  2010/10/12 10:10
    00002  2010/10/12 10:11
«1

Answers

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    This is one way :D
    But I recommend using TableFunction.
    alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';
    
    with t(Val) as(
    select to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select to_date('20101012 10:11','yyyymmdd hh24:mi') from dual)
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from(
    (select Val+NumToDsInterVal(-1+Row_Number() over(partition by Val order by 1),'Minute') as Val from t)
    ))))))))))));
    
    VAL
    ----------------
    2010/10/12 10:10
    2010/10/12 10:11
    2010/10/12 10:12
    2010/10/12 10:13
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I have made TableFunction B-)
    create table dayT(ID,Val) as
    select 1,to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select 1,to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select 1,to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select 1,to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
    select 2,to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select 2,to_date('20101012 10:10','yyyymmdd hh24:mi') from dual;
    
    create or replace type ReturnType as object(
    ID  number(1),
    Val date);
    /
    
    create or replace type ReturnTypeSet as table of ReturnType;
    /
    
    create or replace function MinuteInc return ReturnTypeSet PipeLined IS
        hasChange boolean;
        outR ReturnType := ReturnType(NULL,NULL);
    
        cursor cur is select * from dayT;
        type saveDataDef is table of cur%rowType index by binary_integer;
        saveData saveDataDef;
    begin
        open cur;
        fetch cur bulk collect into saveData;
        close cur;
    
        for I in 1..saveData.Last Loop
            hasChange := false;
            Loop
                for J in 1..saveData.Last Loop
                    continue when I = J;
                    if saveData(I).ID  = saveData(J).ID
                   and saveData(I).Val = saveData(J).Val then
                        saveData(I).Val := saveData(I).Val+ InterVal '1' minute;
                        hasChange := true;
                    end if;
                end Loop;
                exit when hasChange=false;
                hasChange := false;
            end Loop;
        end Loop;
    
        for I in 1..saveData.Last Loop
            outR.ID  := saveData(I).ID;
            outR.Val := saveData(I).Val;
            pipe row(outR);
        end Loop;
    end;
    /
    
    sho err
    
    alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';
    
    select ID,Val from table(MinuteInc)
    order by ID,Val;
    
    ID  Val
    --  ----------------
     1  2010/10/12 10:10
     1  2010/10/12 10:11
     1  2010/10/12 10:12
     1  2010/10/12 10:13
     2  2010/10/12 10:10
     2  2010/10/12 10:11
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,724 Black Diamond
    edited Apr 24, 2010 9:22AM
    Aketi Jyuuzou wrote:
    I have made TableFunction
    Much simpler with MODEL B-) :
    with t as(
              select '00001' id,to_date('20101012 10:10','yyyymmdd hh24:mi') val from dual union all
              select '00001',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
              select '00001',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
              select '00001',to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
              select '00002',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
              select '00002',to_date('20101012 10:10','yyyymmdd hh24:mi') from dual
             )
    select  id,
            val
      from  t
      model
        dimension by(
                     id,
                     row_number() over(partition by id order by val) rn
                    )
        measures(
                 val
                )
        rules(
              val[any,rn > 1] order by id,rn = greatest(val[cv(id),cv(rn)],max(val)[cv(id),rn < cv(rn)] + interval '1' minute)
             )
    /
    
    ID    VAL
    ----- ----------------
    00001 2010/10/12 10:10
    00001 2010/10/12 10:11
    00001 2010/10/12 10:12
    00001 2010/10/12 10:13
    00002 2010/10/12 10:10
    00002 2010/10/12 10:11
    
    6 rows selected.
    
    SQL> 
    SY.
    Solomon Yakobson
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Wow that is very cool solution !!!!!
    That can become more simpler with using partition by(ID) :D
    alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';
    
    with t as(
    select '00001' id,to_date('20101012 10:10','yyyymmdd hh24:mi') val from dual union all
    select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00001',   to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
    select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
    select ID,val
      from t
     model
    partition by(ID)
    dimension by(row_number() over(partition by id order by val) rn)
    measures(val)
    rules(val[rn > 1] order by rn 
        = greatest(val[cv()],
          max(val)[rn < cv()] + interval '1' minute));
    
    ID     val
    -----  ----------------
    00001  2010/10/12 10:10
    00001  2010/10/12 10:11
    00001  2010/10/12 10:12
    00001  2010/10/12 10:13
    00002  2010/10/12 10:10
    00002  2010/10/12 10:11
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Accepted Answer
    Hehe there is more simple one :-)
    alter session set nls_date_format = 'yyyy/mm/dd hh24:mi';
    
    with t as(
    select '00001' id,to_date('20101012 10:10','yyyymmdd hh24:mi') val from dual union all
    select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00001',   to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
    select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
    select ID,Val
      from t
     model
    partition by(ID)
    dimension by(row_number() over(partition by id order by Val) as rn)
    measures(Val)
    rules(Val[rn > 1] order by rn
        = greatest(Val[cv()-1]+interVal '1' minute,
                   Val[cv()]));
    
    ID     VAL
    -----  ----------------
    00001  2010/10/12 10:10
    00001  2010/10/12 10:11
    00001  2010/10/12 10:12
    00001  2010/10/12 10:13
    00002  2010/10/12 10:10
    00002  2010/10/12 10:11
  • 718557
    718557 Member Posts: 54
    Fentastic, yes it's working but a little change,
    I want time as it is means 23:11 it should not convert to 11:11:00 PM

    Please check below function,I thik it's give me wrong output.

    with t as(
    select '00001' id,to_date('20101012 23:10','yyyymmdd hh24:mi') val from dual union all
    select '00001', to_date('20101012 23:10','yyyymmdd hh24:mi') from dual union all
    select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
    select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
    select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
    select ID,Val
    from t
    model
    partition by(ID)
    dimension by(row_number() over(partition by id order by Val) as rn)
    measures(Val)
    rules(Val[rn > 1] order by rn
    = greatest(Val[cv()-1]+interVal '1' minute,
    Val[cv()]));


    Output
    ------
    ID VAL

    00001 10/12/2010 11:10:00 PM
    00001 10/12/2010 11:11:00 PM
    00001 10/12/2010 11:59:00 PM
    00001 10/13/2010
    00002 10/12/2010 10:10:00 AM
    00002 10/12/2010 10:11:00 AM

    But I want output like below.
    --------------------------------

    00001 10/12/2010 23:10
    00001 10/12/2010 23:11
    00001 10/12/2010 23:59
    00001 10/13/2010 23:00
    00002 10/12/2010 10:10
    00002 10/12/2010 10:11
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    I want time as it is means 23:11 it should not convert to 11:11:00 PM
    That has nothing to do with the query.
    Just adjust your NLS_DATE_FORMAT:
    SQL> alter session set nls_date_format='dd/mm/yyyy hh:mi:ss PM';
    
    Session altered.
    
    SQL> with t as(
      2  select '00001' id,to_date('20101012 23:10','yyyymmdd hh24:mi') val from dual union all
      3  select '00001', to_date('20101012 23:10','yyyymmdd hh24:mi') from dual union all
      4  select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
      5  select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
      6  select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
      7  select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
      8  select ID,Val
      9  from t
     10  model
     11  partition by(ID)
     12  dimension by(row_number() over(partition by id order by Val) as rn)
     13  measures(Val)
     14  rules(Val[rn > 1] order by rn
     15  = greatest(Val[cv()-1]+interVal '1' minute,
     16  Val[cv()]));
    
    ID    VAL
    ----- ----------------------
    00001 12/10/2010 11:10:00 PM
    00001 12/10/2010 11:11:00 PM
    00001 12/10/2010 11:59:00 PM
    00001 13/10/2010 12:00:00 AM
    00002 12/10/2010 10:10:00 AM
    00002 12/10/2010 10:11:00 AM
    
    6 rows selected.
    
    SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi';
    
    Session altered.
    
    SQL> with t as(
      2  select '00001' id,to_date('20101012 23:10','yyyymmdd hh24:mi') val from dual union all
      3  select '00001', to_date('20101012 23:10','yyyymmdd hh24:mi') from dual union all
      4  select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
      5  select '00001', to_date('20101012 23:59','yyyymmdd hh24:mi') from dual union all
      6  select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
      7  select '00002', to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
      8  select ID,Val
      9  from t
     10  model
     11  partition by(ID)
     12  dimension by(row_number() over(partition by id order by Val) as rn)
     13  measures(Val)
     14  rules(Val[rn > 1] order by rn
     15  = greatest(Val[cv()-1]+interVal '1' minute,
     16  Val[cv()]));
    
    ID    VAL
    ----- ----------------
    00001 12/10/2010 23:10
    00001 12/10/2010 23:11
    00001 12/10/2010 23:59
    00001 13/10/2010 00:00
    00002 12/10/2010 10:10
    00002 12/10/2010 10:11
    
    6 rows selected.
  • 718557
    718557 Member Posts: 54
    edited Apr 27, 2010 5:51AM
    Yes you are right with the use of alter session set nls_date_format='dd/mm/yyyy hh24:mi'
    I got the result that i want.

    But the problem is that ,
    My NLS_DATE_FORMAT : DD-MON-RR It's set on client machine
    and we have no right's to change it.

    I have got date and time from external table in short
    I got both date and time in separate varchar2 data type column not a date datatype.

    So, is there any other why to achive required output without change NLS_DATE_FORMAT
    or without use the date related function in quty.

    Edited by: user10594896 on Apr 27, 2010 2:50 AM
  • Boneist
    Boneist Member Posts: 4,983 Gold Trophy
    Use to_char to produce whatever format you need:
    with t as(
    select '00001' id,to_date('20101012 10:10','yyyymmdd hh24:mi') val from dual union all
    select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00001',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00001',   to_date('20101012 10:11','yyyymmdd hh24:mi') from dual union all
    select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual union all
    select '00002',   to_date('20101012 10:10','yyyymmdd hh24:mi') from dual)
    select ID, to_char(val, 'yyyy-mm-dd hh24:mi:ss') val
      from t
     model
    partition by(ID)
    dimension by(row_number() over(partition by id order by val) rn)
    measures(val)
    rules(val[rn > 1] order by rn 
        = greatest(val[cv()],
          max(val)[rn < cv()] + interval '1' minute));
    
    ID    VAL                
    ----- -------------------
    00001 2010-10-12 10:10:00
    00001 2010-10-12 10:11:00
    00001 2010-10-12 10:12:00
    00001 2010-10-12 10:13:00
    00002 2010-10-12 10:10:00
    00002 2010-10-12 10:11:00
  • 718557
    718557 Member Posts: 54
    We can not use to_char it's give below error.
    ORA-30081: invalid data type for datetime/interval arithmetic

    Due to we use interVal date related function.
This discussion has been closed.