SQL question

From: dias <ydias_at_hotmail.com>
Date: 18 Jun 2004 04:06:18 -0700
Message-ID: <55a68b47.0406180306.1135d113@posting.google.com>

Hi,

In the following case, I want to select : - for each id, the recent address of type 1 - if this type of address dosn't exist, select the recent address of an other type

drop table T1

create table T1
(id number,

name   		 varchar2(30),
address 	 varchar2(200),
id_type 	 number,
modified 	 date);

insert into T1 values (1, 'AAAA', 'ADR11', 1, sysdate-10);
insert into T1 values (1, 'AAAA', 'ADR12', 1, sysdate-5);
insert into T1 values (1, 'AAAA', 'ADR13', 1, sysdate-1);
insert into T1 values (1, 'AAAA', 'ADR14', 0, sysdate);

insert into T1 values (2, 'BBBB', 'ADR21', 0, sysdate-10); insert into T1 values (2, 'BBBB', 'ADR22', 0, sysdate-1); commit;

SELECT distinct name, address, id_type, modified FROM T1 A
WHERE A.modified = (SELECT MAX (modified) FROM T1 B WHERE A.id = B.id AND B.id_type = 1)
UNION
SELECT distinct name, address, id_type, modified FROM T1 A
WHERE A.modified = (SELECT MAX (modified) FROM T1 B WHERE A.id = B.id)
AND NOT EXISTS
(SELECT *

FROM T1 B
WHERE A.id = B.id
AND B.modified = (SELECT MAX(modified) FROM T1 C WHERE B.id = C.id AND id_type = 1));

This query works fine, but can we do best ?

Thanks Received on Fri Jun 18 2004 - 06:06:18 CDT