Re: Newbie SQL question

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 13 Sep 2001 11:52:30 +0200
Message-ID: <9npvkv$uvc$1@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 - 04:52:30 CDT