Home » SQL & PL/SQL » SQL & PL/SQL » Query to get start and end of completed process (10g)
Query to get start and end of completed process [message #385640] Tue, 10 February 2009 06:05 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
Hi,

I have a table:
LOG_ID	STATUS
------  ----------
1	updating..
2	start
3	inserting..
4	end
5	start
6	updating..
7	end
8	start
9	updating..
10	inserting..
11	end
12	start
13	updating..
14	updating..


Here's the code to create it:
create table sample (log_id number, status varchar2(20));
insert into sample values (1,'updating..');
insert into sample values (2,'start');
insert into sample values (3,'inserting..');
insert into sample values (4,'end');
insert into sample values (5,'start');
insert into sample values (6,'updating..');
insert into sample values (7,'end');
insert into sample values (8,'start');
insert into sample values (9,'updating..');
insert into sample values (10,'inserting..');
insert into sample values (11,'end');
insert into sample values (12,'start');
insert into sample values (13,'updating..');
insert into sample values (14,'updating..');
commit;


Every 'start' marks the beginning of a process and 'end' marks the completion.
If I need to get the log_ids of the 'start' and 'end' of the latest process which completed, my query should return log_ids 8 and 11. What is the most efficient query I can use for this??

Thanks in advance.
Re: Query to get start and end of completed process [message #385643 is a reply to message #385640] Tue, 10 February 2009 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What are the queries you tried?
Is there any index on your table?
What is the number of rows in your table?

Regards
Michel
Re: Query to get start and end of completed process [message #385650 is a reply to message #385643] Tue, 10 February 2009 06:54 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
One more question

Is LOG_ID is Unique/Primary key with sequence.

Thanks
Trivendra
Re: Query to get start and end of completed process [message #385688 is a reply to message #385650] Tue, 10 February 2009 10:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming log_id is a unique, increasing value, this will do it in a single pass of the table:
select distinct
       max_end_id
      ,max(case when status = 'start' then log_id else -1 end) over (order by log_id rows between unbounded preceding and unbounded following) max_start_id
from  (select log_id
             ,status
             ,max(case when status = 'end' then log_id else -1 end) over (order by log_id rows between unbounded preceding and unbounded following) max_end_id
       from   sample
       where  status in ('start','end'))
where log_id <= max_end_id;
Re: Query to get start and end of completed process [message #385794 is a reply to message #385688] Wed, 11 February 2009 01:20 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
A little bit too simple (?) but seems to do the job:
WITH a AS
     (SELECT MAX(log_id) max_end_id
        FROM test_sample
       WHERE status = 'end'),
     b AS
     (SELECT MAX(log_id) max_start_id
        FROM test_sample, a
       WHERE status = 'start' AND log_id < max_end_id)
SELECT max_start_id, max_end_id
  FROM a, b;	   
Previous Topic: Is it possible to generate a text file from client machine ?
Next Topic: locking
Goto Forum:
  


Current Time: Thu Dec 08 21:52:32 CST 2016

Total time taken to generate the page: 0.05344 seconds