Re: Newbie SQL question

Merci beaucoup!

Michel R.

"Michel Cadot" <micadot_at_netcourrier.com> a écrit dans le message news: 9npvkv$uvc$1_at_s1.read.news.oleane.net...
>
> "Existence" <mrochon_at_bigfoot.com> a écrit dans le message news:
> 3MTn7.74344$KE4.8299462_at_carnaval.risq.qc.ca...
> > Hello SQL gurus,
> >
> > I need to do a simple (it sure look simple) query in SQL on a table with
no
> > primary key but I'm having some little difficulties.
> >
> > Basically, I have 3 columns named ID, START_DATE and EMP_AF. Based on
ID
> > which is not unique, I need to extract the value of column EMP_AF which
has
> > the most recent START_DATE for ID.
> >
> > For example, if ID has 5 records with identical values, I need to check
each
> > one in the START_DATE column, get the most recent date and then get to
the
> > corresponding value in EMP_AF for this record.
> >
> > I believe I'm very close to a solution but my head is about to explode
so I
> > need help.
> >
> > Thanks.
> >
> > Michel R.
> >
>
> v817>create table t (id integer, start_date date, emp_af varchar2(10));
> v817>insert into t values (1, sysdate, 'today');
> v817>insert into t values (1, sysdate+1, 'today+1');
> v817>insert into t values (1, sysdate+2, 'today+2');
> v817>insert into t values (1, sysdate-1, 'today-1');
> v817>insert into t values (1, sysdate-2, 'today-2');
> v817>insert into t values (2, sysdate, 'today');
> v817>insert into t values (2, sysdate+3, 'today+3');
> v817>insert into t values (2, sysdate+4, 'today+4');
> v817>insert into t values (2, sysdate-3, 'today-3');
> v817>insert into t values (2, sysdate-4, 'today-4');
> v817>commit;
> v817>select * from t;
>
> ID START_DATE EMP_AF
> ---------- ------------------- ----------
> 1 13/09/2001 11:51:42 today
> 1 14/09/2001 11:51:42 today+1
> 1 15/09/2001 11:51:42 today+2
> 1 12/09/2001 11:51:42 today-1
> 1 11/09/2001 11:51:42 today-2
> 2 13/09/2001 11:51:42 today
> 2 16/09/2001 11:51:42 today+3
> 2 17/09/2001 11:51:42 today+4
> 2 10/09/2001 11:51:42 today-3
> 2 09/09/2001 11:51:42 today-4
>
> 10 rows selected.
>
> v817>select a.id, a.emp_af, a.start_date
> 2 from t a
> 3 where a.start_date = ( select max(b.start_date) from t b
> 4 where b.id = a.id )
> 5 /
>
> ID EMP_AF START_DATE
> ---------- ---------- -------------------
> 1 today+2 15/09/2001 11:51:42
> 2 today+4 17/09/2001 11:51:42
>
> 2 rows selected.
>
> If you have version 8.1.6 and up you can use:
>
> v817>select distinct id,
> 2 last_value(emp_af) over
> 3 (partition by id order by start_date
> 4 rows between unbounded preceding and unbounded following)
> 5 from t
> 6 /
>
> ID LAST_VALUE
> ---------- ----------
> 1 today+2
> 2 today+4
>
> 2 rows selected.
>
> --
> Have a nice day
> Michel
>
>
>
>
Received on Thu Sep 13 2001 - 07:32:50 CDT