Home » SQL & PL/SQL » SQL & PL/SQL » Group consecutive records (Oracle 9i)
icon5.gif  Group consecutive records [message #356083] Wed, 29 October 2008 07:39 Go to next message
Rantoun
Messages: 8
Registered: October 2008
Location: Lebanon
Junior Member

I know what I am asking for is pretty hard, but still maybe there is someone out there who is able to solve my problem!

I have a table that look like this

CREATE TABLE VEHICLETRIPS
 (	VID 		NUMBER(10) 	NOT NULL, 
	VECHIELID 	VARCHAR2(10) 	NOT NULL, 
	DEVICEDATE 	DATE 		NOT NULL,
	TRIPID 		NUMBER(10) 	NOT NULL, 
	COMMUNITY 	VARCHAR2(10) 	NOT NULL
 )  ;


INSERT INTO VEHICLETRIPS values  (  1, 'VA01', to_date ('29-10-08 10:00', 'dd-mm-yy hh24:mi'), 1, 'Area 1' );
INSERT INTO VEHICLETRIPS values  (  2, 'VA01', to_date ('29-10-08 10:06', 'dd-mm-yy hh24:mi'), 1, 'Area 1' );
INSERT INTO VEHICLETRIPS values  (  3, 'VA01', to_date ('29-10-08 10:12', 'dd-mm-yy hh24:mi'), 1, 'Area 1' );
INSERT INTO VEHICLETRIPS values  (  4, 'VA01', to_date ('29-10-08 10:24', 'dd-mm-yy hh24:mi'), 1, 'Area 2' );
INSERT INTO VEHICLETRIPS values  (  5, 'VA01', to_date ('29-10-08 10:30', 'dd-mm-yy hh24:mi'), 1, 'Area 2' );
INSERT INTO VEHICLETRIPS values  (  6, 'VA01', to_date ('29-10-08 10:36', 'dd-mm-yy hh24:mi'), 1, 'Area 3' );
INSERT INTO VEHICLETRIPS values  (  7, 'VA01', to_date ('29-10-08 10:42', 'dd-mm-yy hh24:mi'), 1, 'Area 1' );
INSERT INTO VEHICLETRIPS values  (  8, 'VA01', to_date ('29-10-08 10:48', 'dd-mm-yy hh24:mi'), 1, 'Area 1' );

INSERT INTO VEHICLETRIPS values  (  9, 'VA01', to_date ('29-10-08 11:00', 'dd-mm-yy hh24:mi'), 2, 'Area 3' );
INSERT INTO VEHICLETRIPS values  ( 10, 'VA01', to_date ('29-10-08 11:06', 'dd-mm-yy hh24:mi'), 2, 'Area 3' );
INSERT INTO VEHICLETRIPS values  ( 11, 'VA01', to_date ('29-10-08 11:12', 'dd-mm-yy hh24:mi'), 2, 'Area 2' );
INSERT INTO VEHICLETRIPS values  ( 12, 'VA01', to_date ('29-10-08 11:18', 'dd-mm-yy hh24:mi'), 2, 'Area 2' );


It is the places a vehicle has visited in two trips and the time at which it arrived to that place, so the order of records is very

important. What i want to retrieve is a unique list of places this vehicle has visited and in the order they were visited in.

so if I use this query
QUERY
---------------
select 	row_number() over (partition by tripID order by tripID) id, 
	to_char (min(deviceDate), 'hh24:mi') StartTime , 
	to_char(max(DeviceDate) , 'hh24:mi') STOPTime, 
	TripID, COmmunity 
from vehicleTRips 
group by tripID, community
/


the return value is as follows
RESULT
------
        ID START STOPT     TRIPID COMMUNITY
---------- ----- ----- ---------- ----------
         1 10:00 10:48          1 Area 1
         2 10:24 10:30          1 Area 2
         3 10:36 10:36          1 Area 3
         1 11:12 11:18          2 Area 2
         2 11:00 11:06          2 Area 3


and what i want is as follows...

DESIRED RESULT
--------------
        ID START STOPT     TRIPID COMMUNITY
---------- ----- ----- ---------- ----------
         1 10:00 10:12          1 Area 1
         2 10:24 10:30          1 Area 2
         3 10:36 10:36          1 Area 3
         4 10:42 10:48          1 Area 1
         1 11:12 11:18          2 Area 2
         2 11:00 11:06          2 Area 3


Note that trip 1 has FOUR entries now, Area 1,Area 2, Area 3, and then Area 1 again.

so can anyone help me find the query that will generate the above result!

Thanks in advance...

Re: Group consecutive records [message #356084 is a reply to message #356083] Wed, 29 October 2008 07:43 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This question has been asked many times. Anyways check this link and search for the very last post from Tom (Analytics to the rescue again).

http://www.oracle.com/technology/oramag/oracle/04-mar/o24asktom.html

Regards

Raj
Re: Group consecutive records [message #356088 is a reply to message #356083] Wed, 29 October 2008 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many thanks for the quality of your post.

Regards
Michel
Re: Group consecutive records [message #356091 is a reply to message #356084] Wed, 29 October 2008 08:42 Go to previous messageGo to next message
Rantoun
Messages: 8
Registered: October 2008
Location: Lebanon
Junior Member

Thanks a lot Raj.... I have been searching lots of forums, but it seems the keyword i was using was not helpful!

For those who came across this forum.. here is the final sql statement

select to_char(min(deviceDate), 'hh24:mi:ss'), community from 
(
	select devicedate, community,
	       max(rn) 
	         over(order by devicedate) max_rn
	  from (
		select devicedate, community,
		       case
		       when not (COMMUNITY = LCOMMUNITY) or lcommunity is null
		       then row_num
		        end rn
		  from (
		select devicedate,  community,
		       lag(COMMUNITY)
		          over (order by deviceDate) lCOMMUNITY,
		       row_number()
		          over (order by deviceDate) row_num
		  from vehicletrips
	       )
	)
) group by max_rn, community
/
Re: Group consecutive records [message #356100 is a reply to message #356091] Wed, 29 October 2008 09:01 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Not a problem. Thanks for posting the solution. It's much appreciated. Also thanks for following the forum guidelines and naming the post in a meaningful words.

You are most welcome.

Regards

Raj
Previous Topic: Ask a question
Next Topic: return correct ww
Goto Forum:
  


Current Time: Sat Dec 10 08:58:26 CST 2016

Total time taken to generate the page: 0.10066 seconds