Inner queries

From: Jens Müller <usenet-11-2007_at_tessarakt.de>
Date: Tue, 03 Mar 2009 09:31:48 +0100
Message-ID: <71487lFispn8U1_at_mid.individual.net>

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" ... Received on Tue Mar 03 2009 - 02:31:48 CST