You Asked
Hi, Tom!
I need Your help please.
I have the table t1:
CREATE TABLE t1 (cont_id NUMBER,
block_is_comm NUMBER,
prev_first NUMBER,
prev_last NUMBER,
delta NUMBER,
duration NUMBER,
min_time number,
max_time number);
INSERT INTO t1(cont_id, block_is_comm, prev_first, prev_last, delta, duration)
VALUES (1,1,0,0,5,3);
INSERT INTO t1(cont_id, block_is_comm, prev_first, prev_last, delta, duration)
VALUES (1,1,0,1,10,2);
INSERT INTO t1(cont_id, block_is_comm, prev_first, prev_last, delta, duration)
VALUES (1,0,1,0,3,8);
INSERT INTO t1(cont_id, block_is_comm, prev_first, prev_last, delta, duration)
VALUES (2,1,1,0,10,4);
INSERT INTO t1(cont_id, block_is_comm, prev_first, prev_last, delta, duration)
VALUES (2,0,0,1,20,3);
INSERT INTO t1(cont_id, block_is_comm, prev_first, prev_last, delta, duration)
VALUES (2,0,1,0,5,4);
COMMIT;
SELECT * FROM t1;
CONT_ID BLOCK_IS_COMM PREV_FIRST PREV_LAST DELTA DURATION
1 1 0 0 5 3
1 1 0 1 10 2
1 0 1 0 3 8
2 1 1 0 10 4
2 0 0 1 20 3
2 0 1 0 5 4
I need to find out two values:
1. Min_time is Minimum of delta_time from t1 where (block_is_comm = 1 or prevent_firsts = 0) grouped by cont_id (for cont_id = 1 min_time must be 5, for cont_id = 2 min_time must be 10);
2. Max_time is Maximum of (delta_time + duration) from t1 where (block_is_comm = 1 or prevent_lasts = 0) grouped by cont_id
(for cont_id = 1 max_time must be 12, for cont_id = 2 max_time must be 14);
Question:
1. If it is possible to fetch both Minimum and Maximum in one SELECT?
2. If there is any effective UPDATE for these two fields min_time and max_time to looks data like this:
SELECT CONT_ID, BLOCK_IS_COMM COMM, PREV_FIRST FIRST, PREV_LAST LAST, DURATION DUR, MIN_TIME MIN, MAX_TIME MAX FROM t1;
CONT_ID COMM FIRST LAST DELTA DUR MIN MAX_TIME
1 1 0 0 5 3 5 12
1 1 0 1 10 2 5 12
1 0 1 0 3 8 5 12
2 1 1 0 10 4 10 14
2 0 0 1 20 3 10 14
2 0 1 0 5 4 10 14
Thank You,
Larisa Pupko
and Tom said...
ops$tkyte@ORA10GR2> select cont_id,
2 case when block_is_comm = 1 or prev_first = 0
3 then delta
4 end min_this,
5 case when block_is_comm = 1 or prev_last = 0
6 then delta+duration
7 end max_this
8 from t1
9 where block_is_comm = 1 or prev_first = 0 or prev_last = 0;
CONT_ID MIN_THIS MAX_THIS
---------- ---------- ----------
1 5 8
1 10 12
1 11
2 10 14
2 20
2 9
6 rows selected.
Elapsed: 00:00:00.01
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select cont_id,
2 min(
3 case when block_is_comm = 1 or prev_first = 0
4 then delta
5 end ) min_this,
6 max(
7 case when block_is_comm = 1 or prev_last = 0
8 then delta+duration
9 end ) max_this
10 from t1
11 where block_is_comm = 1 or prev_first = 0 or prev_last = 0
12 group by cont_id;
CONT_ID MIN_THIS MAX_THIS
---------- ---------- ----------
1 5 12
2 10 14
ops$tkyte@ORA10GR2> merge into t1
2 using
3 (
4 select cont_id,
5 min(
6 case when block_is_comm = 1 or prev_first = 0
7 then delta
8 end ) min_this,
9 max(
10 case when block_is_comm = 1 or prev_last = 0
11 then delta+duration
12 end ) max_this
13 from t1
14 where block_is_comm = 1 or prev_first = 0 or prev_last = 0
15 group by cont_id
16 ) t2
17 on (t1.cont_id = t2.cont_id)
18 when matched then update set min_time = min_this, max_time = max_this
19 when not matched /* NEVER HAPPENS! IN 10G would not be necessary */
20 then insert(cont_id) values (null);
6 rows merged.
Elapsed: 00:00:00.03
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select cont_id, min_time, max_time from t1;
CONT_ID MIN_TIME MAX_TIME
---------- ---------- ----------
1 5 12
1 5 12
1 5 12
2 10 14
2 10 14
2 10 14
6 rows selected.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment