Home » SQL & PL/SQL » SQL & PL/SQL » Select rows depending on when they where create
Select rows depending on when they where create [message #277762] Wed, 31 October 2007 07:25 Go to next message
linalar@gmail.com
Messages: 1
Registered: October 2007
Junior Member
I'd like to select all rows in the following table starting with the oldest row and then the row after that which is created at least 10 minutes after the first row, and then the row that is created at least 10 minutes after the second row, and so on. In a interval of 10 minutes starting from the oldest row. Is there a way to do that? Sometimes there will be no rows in the 10 minute span and sometimes there might be 100 000 rows.



PRICE CREATED
147,5 2007-10-26 15:46:46,000000000 <- select this
147,5 2007-10-26 15:36:46,000000000 <- select this
147,5 2007-10-26 15:16:46,000000000
147,5 2007-10-26 15:16:30,000000000 <- select this
147,5 2007-10-26 14:35:48,000000000
148,25 2007-10-26 14:35:48,000000000
148 2007-10-26 14:34:21,000000000
148 2007-10-26 14:34:20,000000000
148 2007-10-26 14:34:20,000000000
148 2007-10-26 14:34:20,000000000 <- select this
148 2007-10-26 14:09:00,000000000 <- select this


Re: Select rows depending on when they where create [message #277764 is a reply to message #277762] Wed, 31 October 2007 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your Oracle version (4 decimals).
Post a test case: create table and insert statements

Regards
Michel
Re: Select rows depending on when they where create [message #277768 is a reply to message #277764] Wed, 31 October 2007 07:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Afraid I'm too busy to look at a solution, but here's the inserts to keep everyone happy.

create table time_Gap (id  number, create_date  date);

insert into  time_gap values(147,to_Date('2007-10-26 15:46:46','yyyy-mm-dd hh24:mi:ss'));
insert into  time_gap values(147,to_Date('2007-10-26 15:36:46','yyyy-mm-dd hh24:mi:ss'));
insert into  time_gap values(147,to_Date('2007-10-26 15:16:46','yyyy-mm-dd hh24:mi:ss'));
insert into  time_gap values(147,to_Date('2007-10-26 15:16:30','yyyy-mm-dd hh24:mi:ss'));
insert into  time_gap values(147,to_Date('2007-10-26 14:35:48','yyyy-mm-dd hh24:mi:ss'));
insert into  time_gap values(148,to_Date('2007-10-26 14:35:48','yyyy-mm-dd hh24:mi:ss'));
insert into  time_gap values(148,to_Date('2007-10-26 14:34:21','yyyy-mm-dd hh24:mi:ss'));
insert into  time_gap values(148,to_Date('2007-10-26 14:34:20','yyyy-mm-dd hh24:mi:ss'));
insert into  time_gap values(148,to_Date('2007-10-26 14:34:20','yyyy-mm-dd hh24:mi:ss'));
insert into  time_gap values(148,to_Date('2007-10-26 14:34:20','yyyy-mm-dd hh24:mi:ss'));
insert into  time_gap values(148,to_Date('2007-10-26 14:09:00','yyyy-mm-dd hh24:mi:ss')); 
Re: Select rows depending on when they where create [message #277773 is a reply to message #277768] Wed, 31 October 2007 08:11 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks JRowbottom.

OP,
I don't understand why rows with same date are not all selected?

SQL> with 
  2    data as (
  3      select price, create_date,
  4               count(*) over 
  5                 (order by create_date
  6                  range between 1/144-1/86400 preceding and current row) 
  7             - count(*) over 
  8               (order by create_date
  9                range between current row and current row) 
 10               cnt
 11      from time_gap
 12    )
 13  select price, create_date
 14  from data
 15  where cnt = 0
 16  order by create_date
 17  /
     PRICE CREATE_DATE
---------- -------------------
       148 26/10/2007 14:09:00
       148 26/10/2007 14:34:20
       148 26/10/2007 14:34:20
       148 26/10/2007 14:34:20
       147 26/10/2007 15:16:30
       147 26/10/2007 15:36:46
       147 26/10/2007 15:46:46

7 rows selected.

Regards
Michel
Previous Topic: Looping through all records of an unknown table
Next Topic: Creating Index while Adding Constraint
Goto Forum:
  


Current Time: Sat Dec 03 15:52:18 CST 2016

Total time taken to generate the page: 0.08350 seconds