Re: Help needed to find minimum in a series

From: Paul Stewart <stewart.paul_at_gmail.com>
Date: 17 Apr 2007 06:09:03 -0700
Message-ID: <1176815343.703639.76330@b75g2000hsg.googlegroups.com>

drop table table_1;
drop table table_2;

create table table_1 (userid number, status varchar2(4));

create table table_2 (userid number, status varchar2(4), start_date date);

insert into table_1 (userid, status) values (1234, 'A001'); insert into table_1 (userid, status) values (3456, 'C001');

insert into table_2 (userid, status, start_date) values
(1234,'A001',to_date('01-01-2007','dd-mm-yyyy'));
insert into table_2 (userid, status, start_date) values
(1234,'A001',to_date('01-01-2006','dd-mm-yyyy'));
insert into table_2 (userid, status, start_date) values
(1234,'B001',to_date('01-01-2005','dd-mm-yyyy'));
insert into table_2 (userid, status, start_date) values
(1234,'A001',to_date('01-01-2004','dd-mm-yyyy'));

commit;

select * from table_1;
select * from table_2;

select a.userid

,      a.status
,      min(b.start_date) start_date
from   table_1 a
,      table_2 b

where a.userid = b.userid
group by a.userid
, a.status;

On Apr 17, 3:00 pm, Thomas Sommerfeld <TSommerf..._at_gmx.de> wrote:
> Sandy80 schrieb:
>
> > Hi,
>
> > I have the following two tables:
>
> > Table 1
> > Userid Status
> > 1234 A001
> > 3456 C001
>
> > Table 2
> > Userid Status Start Date
> > 1234 A001 01/01/2007
> > 1234 A001 01/01/2006
> > 1234 B001 01/01/2005
> > 1234 A001 01/01/2004
>
> > Now what I want to get is the minimum date from table 2 for a userid
> > where his status in table 2 is the same his status in table 1 i.e. for
> > userid 1234 since his status is A001 in table 1 so I want the the date
> > on which his status A001 started in table 2 which means I want the
> > date '01/01/2006.
> > Can someone suggest how to frame this query.
> > Any help is welcome!!!
>
> Hi Sandy,
>
> select Table1.Userid, Table1.Status, min(Table2.StartDate)
> from Table1 inner join Table2 on Table1.Userid = Table2.Userid and
> Table1.Status = Table2.Status
> group by Table1.Userid, Table1.Status;
>
> HTH
> Thomas
>
> --
> For answers by personal mail use: thomas.sommerfeld at domain ust-gmbh.de
Received on Tue Apr 17 2007 - 08:09:03 CDT