Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!priapus.visi.com!news-out.visi.com!petbe.visi.com!proxad.net!wanadoo.fr!not-for-mail
From: "Joe Smith" <nospam@nospam.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: (Newbie) Help with query.
Date: Fri, 6 Jun 2003 10:20:17 +0200
Organization: Wanadoo, l'internet avec France Telecom
Lines: 67
Message-ID: <bbpira$n4l$1@news-reader14.wanadoo.fr>
References: <bbnlif$njb$1@news-reader12.wanadoo.fr> <MPG.19490165e03512ad9897ab@news.la.sbcglobal.net> <bbno96$pvl$1@news-reader13.wanadoo.fr> <MPG.19494185fdfa19619897b0@news.la.sbcglobal.net>
NNTP-Posting-Host: aputeaux-103-1-1-8.w193-252.abo.wanadoo.fr
X-Trace: news-reader14.wanadoo.fr 1054887594 23701 193.252.196.8 (6 Jun 2003 08:19:54 GMT)
X-Complaints-To: abuse@wanadoo.fr
NNTP-Posting-Date: 6 Jun 2003 08:19:54 GMT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Xref: core-easynews comp.databases.oracle.misc:98438
X-Received-Date: Fri, 06 Jun 2003 01:19:11 MST (news.easynews.com)


"Karsten Farrell" <kfarrell@belgariad.com> wrote in message

> > select A.ts, a, b from A, B where B.ts>A.ts
> > This returns, for each a, all the b for which B.ts>A.ts...
> > I can't limit it with rownum
> >
> > I've also tried with a subselect:
> > select A.ts as myts, a, b from A, (select ...)  where ...
> > select A.ts as myts, a, b from A, B where A.ts=(select ...)
> >
> > the problem is that I can't reference myts inside the subselect
> >

>
> TS                 A
> --------- ----------
> 04-JUN-03         -1
> 05-JUN-03          0
> 06-JUN-03          1
> 07-JUN-03          2
>
> 4 rows selected.
>
> SQL> select * from b;
>
> TS                 B
> --------- ----------
> 31-MAY-03       -500
> 02-JUN-03       -300
> 04-JUN-03       -100  <-- first b row > min a row
> 06-JUN-03        100
> 08-JUN-03        300
> 10-JUN-03        500
>
Thanks for your answer.
Well, in fact, I will have to explain myself better:
In the first table, there will be events of one type, and in the other one,
events of other type that have followed.
TS                       A
===============
04-jun-03 10:30    -1
04-jun-03 10:35     0
04-jun-03 10:38     1
04-jun-03 10:41     2

TS                       B
===============
04-jun-03 10:31   128
04-jun-03 10:36   129
04-jun-03 10:39   130
04-jun-03 10:42   131

So the list I'm trying to get is:

TS                       A                B
=======================
04-jun-03 10:30    -1           128
04-jun-03 10:35     0            129
04-jun-03 10:38     1            130
04-jun-03 10:41     2            131

Is this feasible?

Thanks!