Re: selecting _only_ the most recent entry

From: Timo Biesenbach <tbiesenb_at_igd.fhg.de>
Date: Tue, 07 Sep 1999 14:02:53 +0200
Message-ID: <37D4FEED.3983E4AA@igd.fhg.de>

Hi,

try this statement :

select * from employee e, phone p, address a

where  a.empno = e.empno
and    p.empno = e.empno
and    a.recdate = (select max(recdate) from address where a.empno =

e.empno)
and p.recdate = (select max(recdate) from phone where p.empno = e.empno);

I tried it on Oracle 8 with the following table layout :

SQL> desc employee

 Name                                                  Null?    Type
 ----------------------------------------------------- --------
------------------------------------
 EMPNO                                                 NOT NULL
NUMBER(38)
 EMPNAME                                                       
VARCHAR2(40)
 EMPDATE                                                        DATE

SQL> desc address
 Name                                                  Null?    Type
 ----------------------------------------------------- --------
------------------------------------
 EMPNO                                                 NOT NULL
NUMBER(38)
 STREET                                                        
VARCHAR2(40)
 RECDATE                                               NOT NULL DATE

SQL> desc phone
 Name                                                  Null?    Type
 ----------------------------------------------------- --------
------------------------------------
 EMPNO                                                 NOT NULL
NUMBER(38)
 PHONENO                                                       
VARCHAR2(40)
 RECDATE                                               NOT NULL DATE

Have fun,
timo

bookouri_at_hotmail.com wrote:
>
> I have a similar problem, except that I want to select only the most
> recent record in a table.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

--

+---+---+-+-+-+-+
|   |   |-+-+-+-+ Fraunhofer-Institut fuer Graphische Datenverarbeitung
+---+---+-+-+-+-+ Fraunhofer-Institute for Computer Graphics
|   |   |-+-+-+-+ Abteilung / Department : Document Imaging  
+---+---+-+-+-+-+
|      __   _   | Timo Biesenbach
|  !  ! _  ! \  | Rundeturmstr. 6      phone  ++49 6151 155 578
|  !  !__! !_/  | 64283 Darmstadt      fax    ++49 6151 155 299 
+---------------+ Germany              email  timo.biesenbach_at_igd.fhg.de

Received on Tue Sep 07 1999 - 07:02:53 CDT