I have a table containing some GPS points with their position and a grouping identifier: (In this example I'm using ID as a sorting column - in the real life I have a timestamp column too):
ID POSITION GROUP_ID
(SDO_GEOMETRY)
----------------------------------
1 (point w/coords 1) 1
2 (point w/coords 2) 1
3 (point w/coords 3) 1
4 (point w/coords 4) 2
5 (point w/coords 5) 3
6 (point w/coords 6) 3
7 (point w/coords 7) 3
8 (point w/coords 8) 3
9 (point w/coords 9) 3
10 (point w/coords 10) 4
11 (point w/coords 11) 4
12 (point w/coords 12) 4
I want to add to each record the position of the FIRST point in its group:
ID POSITION GROUP_ID FIRST_POSITION
(SDO_GEOMETRY) (SDO_GEOMETRY)
-----------------------------------------------------
1 (point w/coords 1) 1 (point w/coords 1)
2 (point w/coords 2) 1 (point w/coords 1)
3 (point w/coords 3) 1 (point w/coords 1)
4 (point w/coords 4) 2 (point w/coords 2)
5 (point w/coords 5) 3 (point w/coords 3)
6 (point w/coords 6) 3 (point w/coords 3)
7 (point w/coords 7) 3 (point w/coords 3)
8 (point w/coords 8) 3 (point w/coords 3)
9 (point w/coords 9) 3 (point w/coords 3)
10 (point w/coords 10) 4 (point w/coords 4)
11 (point w/coords 11) 4 (point w/coords 4)
12 (point w/coords 12) 4 (point w/coords 4)
I tried the following:
select ID, POSITION, GROUP_ID,
FIRST_VALUE(POSITION) over(partition by GROUP_ID ORDER BY ID) FIRST_POSITION
from points
but I get the following error:
ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type
Reading this I guess Oracle needs to be able to sort by POSITION in addition to ID.
Sure I could get the first ID and then join to the same table to lookup the position. Somethig like this:
with p as
select p.ID, p.POSITION, p.GROUP_ID, pp.POSITION FIRST_POSITION
from (
select ID, POSITION, GROUP_ID,
FIRST_VALUE(ID) over(partition by GROUP_ID ORDER BY ID) FIRST_ID
from points
) p inner join points pp on (pp.ID = p.ID)
but is there a better way to get FIRST_POSITION?