Forum Stats

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

Discussions

range query

Alfio
Alfio Member Posts: 6
edited Nov 2, 2010 10:27PM in SQL & PL/SQL
I have a table:

CREATE TABLE TEST
(
name VARCHAR2(10 BYTE),
num NUMBER
)

select * from test order by num;
name, num
A, 1 *
B, 4
C, 8
D, 18 *
h, 21
E, 34 *
f, 35
t, 36
L, 51 *
Z, 55

I need a query to get records where the num value >= previous got num value + 15
(the records to find are marked with *)
The first record is always valid.
In the example above, I don't get record (C, 8) because the difference with num value of record (A,1) is less then 15,
but i get record (L, 51) because the difference with num value of record (E,34) is more then 15.

thanks

Best Answer

  • 450441
    450441 Member Posts: 2,525
    Accepted Answer
    Why is the second number 30 and not 29?

    According to the original post, it must be >= the previously selected number + 15.

    14+15 = 29.

    There's an entry for 29, so the second record should be that one.

    Assuming I'm right, you just need to "adjust" your window by the 1st value.
    with test as (
    select 'A' nam,  14 num from dual union all
    select 'B',  15 from dual union all
    select 'C',  19 from dual union all
    select 'D',  29 from dual union all
    select 'E',  30 from dual union all
    select 'F',  50 from dual union all
    select 'G',  51 from dual)
    select num
    from (select trunc((num-minnum)/15), min(num) num
          from test, (select min(num) minnum from test) x
          group by trunc((num-minnum)/15)
         )
    
    NU
    --
    14
    29
    50

Answers

  • Keith Jamieson
    Keith Jamieson Member Posts: 2,815
    How do you detrmine which row to compare with.
    Its clearly not the previous row judging by your example.

    Here's a pointer. (Analytics )
    with testdata as
    (
    select 'A' name, 1 num from dual union all
    select 'B', 4 from dual union all
    select 'C', 8 from dual union all
    select 'D', 18  from dual union all
    select 'h', 21 from dual union all
    select 'E', 34  from dual union all
    select 'f', 35 from dual union all
    select 't', 36 from dual union all
    select 'L', 51  from dual union all
    select 'Z', 55 from dual 
    )
    select name,num
    from
    (
    select name,num,lag(num,1,0) over (partition by 1 order by 1) prev_num from testdata
    order by num
    )
    where num >= (prev_num+15)
  • 450441
    450441 Member Posts: 2,525
    To phrase it differently, don't you just want the lowest number in each "bucket" of 15?
    select num
    from (select trunc(num/15), min(num)
          from test
          group by trunc(num/15)
         )
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    How about this:
    SQL> edit
    
    Wrote file sqlplus_buffer.sql
    
    
    
      1  WITH test AS
    
      2  (
    
      3     SELECT 'A' AS name, 1 AS num FROM DUAL UNION ALL
    
      4     SELECT 'B' AS name, 4 AS num FROM DUAL UNION ALL
    
      5     SELECT 'C' AS name, 8 AS num FROM DUAL UNION ALL
    
      6     SELECT 'D' AS name, 18 AS num FROM DUAL UNION ALL
    
      7     SELECT 'h' AS name, 21 AS num FROM DUAL UNION ALL
    
      8     SELECT 'E' AS name, 34 AS num FROM DUAL UNION ALL
    
      9     SELECT 'f' AS name, 35 AS num FROM DUAL UNION ALL
    
     10     SELECT 't' AS name, 36 AS num FROM DUAL UNION ALL
    
     11     SELECT 'L' AS name, 51 AS num FROM DUAL UNION ALL
    
     12     SELECT 'Z' AS name, 55 AS num FROM DUAL
    
     13  )
    
     14  SELECT a.name,a.num,a.diff
    
     15  FROM
    
     16  (SELECT name,num,num-(LAG(num,1) OVER (order by num)) AS diff FROM test order by num) a
    
     17* WHERE a.diff >= 15 OR a.diff IS NULL
    
    SQL> /
    
    
    
    N        NUM       DIFF
    
    - ---------- ----------
    
    A          1
    
    L         51         15
    
    
    
    SQL>
    I hope this helps. I had a hard time understanding your requirements.
  • Alfio
    Alfio Member Posts: 6
    Thanks, but the solution is not correct.
    Sorry, but the problem is very difficult to explain.

    the range(15) start from the first record, so if the first number is 14, the second number will be >= 30.
    Other example:

    A, 14
    B, 15
    C, 19
    D, 29
    E, 30
    F, 50
    G, 51

    I want select only 3 records: (A,14) , (E, 30) , (F, 50)

    I hope that this example is more clearly.
  • 450441
    450441 Member Posts: 2,525
    Accepted Answer
    Why is the second number 30 and not 29?

    According to the original post, it must be >= the previously selected number + 15.

    14+15 = 29.

    There's an entry for 29, so the second record should be that one.

    Assuming I'm right, you just need to "adjust" your window by the 1st value.
    with test as (
    select 'A' nam,  14 num from dual union all
    select 'B',  15 from dual union all
    select 'C',  19 from dual union all
    select 'D',  29 from dual union all
    select 'E',  30 from dual union all
    select 'F',  50 from dual union all
    select 'G',  51 from dual)
    select num
    from (select trunc((num-minnum)/15), min(num) num
          from test, (select min(num) minnum from test) x
          group by trunc((num-minnum)/15)
         )
    
    NU
    --
    14
    29
    50
  • Keith Jamieson
    Keith Jamieson Member Posts: 2,815
    Ah I got you now

    Always choose first number
    Second number is first number that is 15 greater than first number
    3rd number is first number that is 15 greater than second number.
    etc

    which gives
    A, 14
    E, 30
    F, 50
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Nov 2, 2010 10:27PM
    I solved same question :-)
    673100

    and my site mentions same question B-)
    http://www.geocities.jp/oraclesqlpuzzle/10-245.html
    Aketi Jyuuzou
  • Alfio
    Alfio Member Posts: 6
    thanks, very good, very well ...

    I have an other question ....
    I have the same scenario but more complicated, I have 3 columns: surname, name, datatime
    and I have to use the same reasoning but for each surname:
    example:
    the range is 15 minuts

    surname, name, datatime (dd/mm/yyyy hh24.mi)
    x, a, 03/09/2008 10.00
    x, b, 03/09/2008 10.08
    x, c, 03/09/2008 10.16
    y, a, 03/09/2008 10.10
    y, b, 03/09/2008 10.30
    y, c, 03/09/2008 10.50

    i want select 5 record:
    (x, a, 03/09/2008 10.00), (x, c, 03/09/2008 10.16) , (y, a, 03/09/2008 10.10) , (y, b, 03/09/2008 10.30) ,
    (y, c, 03/09/2008 10.50)

    thanks
  • JonWat
    JonWat Member Posts: 536 Silver Badge
    I don't think that is the correct answer. I think that will only work if you always manage to stay at the bottom boundary of your window (e.g. 14, 29, 44, 59 etc.)

    It fails with the following data (gives you 40 and 45).

    Aketi Jyuuzu's method works. Method is to work out what the next row would be if any given row is chosen (i.e. the first row that is at least 15 away), and then use a hierarchical query to choose the rows you would actually need starting with whatever is your first row.

    (actually need to create a table so you can use rowids)

    create table testxx as (
    select 'A' nam, 14 num from dual union all
    select 'B', 15 from dual union all
    select 'C', 19 from dual union all
    select 'D', 40 from dual union all
    select 'E', 41 from dual union all
    select 'F', 45 from dual union all
    select 'G', 56 from dual);


    with WorkView as (
    Select nam,num,RowID as Row_ID,min(num) over() as startnum,
    first_value(RowID) over(order by num
    range between 15 following
    and unbounded following) as childRowID
    from Testxx)
    select nam,num
    from WorkView
    Start With num = startnum
    connect by prior childRowID = Row_ID;
This discussion has been closed.