RE: Query help
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <angani@xxxxxxxxx>, "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 1 Mar 2014 12:42:14 -0500
Hmm. Well first I'm wondering whether there is an important reason in your
use why you're mixing together what are apparently two different relations
in a single table.
IF (important key word, IF) there is always a pair of c2=1,
c6='configuration' and/or c2=0, c6='shotList' for each c1 (which seems
LIKELY from your data below, but which is by no means guaranteed, therefore
the IF)
THEN this screams to be two different tables. I suppose if you have many
additional types there could be a too many tables argument against this
separation. If there is a 1 to 1 relationship between c2 and c6 though, I
can't fathom why you'd have them both. Even if you have a lot of types, at
lookup table T1_t (c2,c6) would suffice. I'm gonna leave that out for the
moment.
IF matching c1 values are always the same c3, then this quite long string
seems to call for a third table. (This is *possibly* a reason for a three
table cluster, depending on your full real data, by the way.)
Perhaps T1_c (c1 int, c4 int, c5 int, primary key (c1));
T1_s (c1 int, c4 int, c5 int, primary key (c1));
T1_l (c1 int, c3 varchar2(1000), primary key (c1));
select * from t1;
would then be (among other possibilities):
select l.c1, '1', l.c3, c.c4, c.c5, 'configuration'
from t1_l l, t1_c c
where l.c1 = c.c1
union all
select l.c1,'0',l.c3, s.c4, s.c5,'shotList'
from t1_l l, t1_s s;
and the subsequent query could be:
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;
select l.c3, m.mc5, m.c4
from t1_l l,
(select c.c1 mc1, greater(c.c5,s.c5) mc5, greater(c.c4,s.c4) mc4
from t1_c c, t1_s s
-- likely you want 576 below in a bind variable or such, but that is a
different question
where c.c1 = 576
and s.c1 = 576
and c.c1 = s.c1 ) m
where l.c1 = m.mc1;
Now of course you probably want a review using your existing data model.
Still there is the question of whether c1 and c3 are pairs. That is, for
each c1, is there exactly one c3 (log file)?
IF so, then you'd be far better off to group by c1, do the join as a
subquery, and only bring back the log file at the top of the query. Since
your pk rules out multiples, you could go with greater from two subqueries
getting c4 and c5 for the two types joined on c1, and thus do away with the
group by and having count altogether.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Raju Angani
Sent: Friday, February 28, 2014 7:45 PM
To: ORACLE-L
Subject: Query help
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,'configurat
ion');
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,'configurat
ion');
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,'configurat
ion');
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,'configurat
ion');
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
Other related posts:
