Discussions
Categories
 381.9K All Categories
 2.1K Data
 207 Big Data Appliance
 1.9K Data Science
 447.4K Databases
 220.8K General Database Discussions
 3.7K Java and JavaScript in the Database
 23 Multilingual Engine
 516 MySQL Community Space
 463 NoSQL Database
 7.8K Oracle Database Express Edition (XE)
 2.9K ORDS, SODA & JSON in the Database
 469 SQLcl
 3.9K SQL Developer Data Modeler
 185.9K SQL & PL/SQL
 20.9K SQL Developer
 292.6K Development
 7 Developer Projects
 124 Programming Languages
 289.4K Development Tools
 95 DevOps
 3K QA/Testing
 645.5K Java
 23 Java Learning Subscription
 36.9K Database Connectivity
 150 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.8K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 16 Java Essentials
 143 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 197 Java User Groups
 24 JavaScript  Nashorn
 Programs
 242 LiveLabs
 35 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.8K Other Languages
 2.3K Chinese
 166 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 225 Portuguese
range query
Alfio
Member Posts: 6
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
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

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((numminnum)/15), min(num) num from test, (select min(num) minnum from test) x group by trunc((numminnum)/15) ) NU  14 29 50
Answers

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)

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) )

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. 
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. 
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((numminnum)/15), min(num) num from test, (select min(num) minnum from test) x group by trunc((numminnum)/15) ) NU  14 29 50

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 
I solved same question :)
673100
and my site mentions same question B)
http://www.geocities.jp/oraclesqlpuzzle/10245.html 
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 
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.