Re: Inner queries

#1

On 03.03.2009 09:31, Jens Müller wrote:

Quote:

Hi,

I have this query:

select u1.user_id, TO_CHAR(u1.LOCATION_DATE, 'YYYY-MM-DD HH24:MI:SS'),
(select min(sdo_geom.sdo_distance(u1.location, u2.location, 0.005,
'unit=M'))
from user_tracks u2
where u1.user_id <> u2.user_id and
u2.user_id between 9172 and 9181
and u2.location_date in
(select max(u3.location_date)
from user_tracks u3
where u3.location_date < u1.location_date
and u3.user_id = u2.user_id
)
) min_distance
from user_tracks u1
where u1.user_id between 9172 and 9181;

user_tracks contains position information about users, and I want the
nearest neighbor for each position (based on the latest position of that
user before u1.location_date).

That one above works fine - but how to find the 2nd-nearest neighbor?

Not sure whether that would work but you could try to use analytic SQL -
particularly ROW_NUMBER with ordering by distance. And then you add a
join criterion where that column has value 2.

Kind regards

robert

#2

Jens Müller schreef:

Quote:

Hi,

I have this query:

select u1.user_id, TO_CHAR(u1.LOCATION_DATE, 'YYYY-MM-DD HH24:MI:SS'),
(select min(sdo_geom.sdo_distance(u1.location, u2.location, 0.005,
'unit=M'))
from user_tracks u2
where u1.user_id <> u2.user_id and
u2.user_id between 9172 and 9181
and u2.location_date in
(select max(u3.location_date)
from user_tracks u3
where u3.location_date < u1.location_date
and u3.user_id = u2.user_id
)
) min_distance
from user_tracks u1
where u1.user_id between 9172 and 9181;

user_tracks contains position information about users, and I want the
nearest neighbor for each position (based on the latest position of that
user before u1.location_date).

That one above works fine - but how to find the 2nd-nearest neighbor?

This:

select u1.user_id, TO_CHAR(u1.LOCATION_DATE, 'YYYY-MM-DD HH24:MI:SS'),
u1.aufenthaltsdauer,
(select * from

(select sdo_geom.sdo_distance(u1.location, u2.location, 0.005,
'unit=M') abcde
from user_tracks u2
where u1.userid <> u2.user_id and
u2.user_id between 9172 and 9181
and u2.location_date in
(select max(u3.location_date)
from user_tracks u3
where u3.location_date < u1.location_date
and u3.user_id = u2.user_id
)
and rownum < 2
order by abcde
) min_distance
)
from user_tracks u1
where u1.user_id between 9172 and 9181;

does not work - invalid identifier "U1"."USERID" at this point: "where
u1.userid <> u2.user_id" ...

That is because you forgot an underscore in userid, should be user_id....

Shakespeare

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Inner queries

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Inner queries - 03-03-2009 , 02:59 AM

Re: Inner queries - 03-03-2009 , 03:44 AM