Re: Inner queries

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Tue, 03 Mar 2009 09:59:08 +0100
Message-ID: <7149rdFj009iU1_at_mid.individual.net>

On 03.03.2009 09:31, Jens Müller wrote:
> 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 Received on Tue Mar 03 2009 - 02:59:08 CST