Re: (Newbie) Help with query.

nospam_at_nospam.com said...
> Happily, I don't have to do homework anymore.
> But I don't earn my life making SQL, either.
>
> 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
>
> Yes, I guess it must be pretty obvious, but it's the first time I face this
> "obvious" select.
> Thanks anyway for answering.
>
> "Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
> news:MPG.19490165e03512ad9897ab_at_news.la.sbcglobal.net...
> > nospam_at_nospam.com said...
> > > Hi,
> > >
> > > I'm not sure about how to accomplish the following query:
> > > Table A
> > > ======
> > > Date ts
> > > Number a
> > >
> > > Table B
> > > ======
> > > Date ts
> > > Number b
> > >
> > > I want to show A.ts, a, b , being b the first record (and only the first
> > > one) such as B.ts > A.ts
> > >
> > > Thanks in advance!
> > >
> > >
> > >
> > This is so simple, it MUST BE homework. What have you tried so far?
> > --
> > /Karsten
> > DBA > retired > DBA
>

I stand corrected. You have to admit, it does look suspiciously like homework. I was hoping someone else would answer this with a better example, but...

Is the following something like what you're trying to do?

SQL> create table a(ts date, a number);
Table created.

SQL> insert into a values (sysdate-1, -1); 1 row created.

SQL> insert into a values (sysdate, 0);
1 row created.

SQL> insert into a values (sysdate+1, 1); 1 row created.

SQL> insert into a values (sysdate+2, 2); 1 row created.

SQL> create table b(ts date, b number);
Table created.

SQL> insert into b values (sysdate-5, -500); 1 row created.

SQL> insert into b values (sysdate-3, -300); 1 row created.

SQL> insert into b values (sysdate-1, -100); 1 row created.

SQL> insert into b values (sysdate+1, 100); 1 row created.

SQL> insert into b values (sysdate+3, 300); 1 row created.

SQL> insert into b values (sysdate+5, 500); 1 row created.

SQL> select * from a;

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

6 rows selected.

SQL> select a.ts, a, b
  2 from a, b
  3 where b.ts > (select min(a.ts) from a)   4 and rownum = 1;

TS                 A          B
--------- ---------- ----------
04-JUN-03         -1       -100

1 row selected.

SQL>

-- 
/Karsten
DBA > retired > DBA