Query help
Date: Fri, 28 Feb 2014 16:45:28 -0800
Message-ID: <CAJ-04Or3upMAz5467Szqi9iQToXzweWE66F3z2h9mY2LygbF9w_at_mail.gmail.com>
Hi SQL Gurus,
Is there a alternative(better) way to write the below query?
drop table t1;
create table T1(C1 INT, C2 INT, C3 VARCHAR2(1000), C4 INT, C5 INT, C6
VARCHAR2(512),
primary key (c1,c2));
create index idx_t1_c3 on t1(c3,c4);
INSERT INTO T1
VALUES(576,1,'C://VOL/516AFE-345D1-TEEW-130.190_0.LOG',1975,1975,'configuration');
INSERT INTO T1
VALUES(576,0,'C://VOL/516AFE-345D1-TEEW-130.190_0.LOG',0,0,'shotList');
INSERT INTO T1
VALUES(577,1,'C://VOL/516AFE-345D1-TEEW-130.180_0.LOG',2345,2345,'configuration');
INSERT INTO T1
VALUES(577,0,'C://VOL/516AFE-345D1-TEEW-130.180_0.LOG',0,0,'shotList');
INSERT INTO T1
VALUES(578,1,'C://VOL/516AFE-345D1-TEEW-130.170_0.LOG',2346,2345,'configuration');
INSERT INTO T1
VALUES(578,0,'C://VOL/516AFE-345D1-TEEW-130.170_0.LOG',0,0,'shotList');
INSERT INTO T1
VALUES(579,1,'C://VOL/516AFE-345D1-TEEW-130.160_0.LOG',2345,2345,'configuration');
INSERT INTO T1
VALUES(579,0,'C://VOL/516AFE-345D1-TEEW-130.190_0.LOG',0,0,'shotList');
select * from T1;
SELECT t1.C3, MAX(t1.C5), MAX(t1.C4)
FROM T1 t1, T1 tmp
WHERE tmp.C1 = 576
AND tmp.C6 IN ('configuration', 'shotList')
AND tmp.C3 = t1.C3
GROUP BY t1.C3
HAVING COUNT(t1.C1) > 1;
Thank you
RA
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Mar 01 2014 - 01:45:28 CET