Group consecutive records

[Rantoun]

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(Dev iceDate) , '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

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

Thanks in advance...

[Rantoun]

Ok got an answer for this one... here is it for those of you who come across this post...

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