OraFAQ Forum: SQL & PL/SQL » Select rows depending on when they where create

Home » SQL & PL/SQL » SQL & PL/SQL » Select rows depending on when they where create

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select rows depending on when they where create [message #277762]

Wed, 31 October 2007 07:25

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

Report message to a moderator

Re: Select rows depending on when they where create [message #277764 is a reply to message #277762]

Wed, 31 October 2007 07:29

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post your Oracle version (4 decimals).
Post a test case: create table and insert statements

Regards
Michel

Report message to a moderator

Re: Select rows depending on when they where create [message #277768 is a reply to message #277764]

Wed, 31 October 2007 07:43

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

Report message to a moderator

Re: Select rows depending on when they where create [message #277773 is a reply to message #277768]

Wed, 31 October 2007 08:11

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 08 20:57:53 CST 2025