Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambrium.nl!feeder6.cambrium.nl!feeder8.cambrium.nl!feed.tweaknews.nl!193.201.147.71.MISMATCH!xlned.com!feeder3.xlned.com!news2.euro.net!newsfeed.freenet.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: Robert Klemme <shortcutter@googlemail.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Inner queries
Date: Tue, 03 Mar 2009 09:59:08 +0100
Lines: 38
Message-ID: <7149rdFj009iU1@mid.individual.net>
References: <71487lFispn8U1@mid.individual.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
X-Trace: individual.net 9kez4W7Z4w6LC4KV4DprYA3xt39AOp8tQ/o3L8WN5p0tUoyDU=
Cancel-Lock: sha1:txmf+CeULzh+zzudhTgiow8HvYY=
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
In-Reply-To: <71487lFispn8U1@mid.individual.net>
X-Antivirus: avast! (VPS 090302-0, 02.03.2009), Outbound message
X-Antivirus-Status: Clean
Xref:  news.cambrium.nl

On 03.03.2009 09:31, Jens M=FCller wrote:
> Hi,
>=20
> I have this query:
>=20
> 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=3DM'))
>   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 =3D u2.user_id
>      )
>   ) min_distance
> from user_tracks u1
> where u1.user_id between 9172 and 9181;
>=20
>=20
> user_tracks contains position information about users, and I want the=20
> nearest neighbor for each position (based on the latest position of tha=
t=20
> user before u1.location_date).
>=20
> 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=20
join criterion where that column has value 2.

Kind regards

	robert