# Thread: Help - Query to Find the min and max value at the breaks

1. Registered User
Join Date
Jan 2005
Posts
2

## Unanswered: Help - Query to Find the min and max value at the breaks

Hi,
I'm facing with one simple(seems to be) but typical problem with one table.
The current table is

Table : Table1
Columns :
A number(10) not null
B number(10) not null.
The Values in the above table is

A B
-----------------
1 1
2 2
3 3
9 9 <------------------- first break in values
10 10
11 11
12 12
18 18 <-------------------- second break in values
19 19
20 20
28 28
29 29 <--------------------- third break in values

Now my problem is to find out the min and max values of column A and B
before and after each breaks
output ------>
Before first break
A - min =1
A - max=3
Before second break
A - min =9
A-max =12
Before third break
A - min =18
A - max = 29....

I'm really stuck with to find any a way out so any SQL
gives me the above solution.Any help is really appreciated....
Indranil

2. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
This query will return result you need:
Code:
```SELECT MIN(a), MAX(a)
FROM TABLE1
WHERE a < 9
UNION
SELECT MIN(a), MAX(a)
FROM TABLE1
WHERE a >= 9 AND a < 18
UNION
SELECT MIN(a), MAX(a)
FROM TABLE1
WHERE a >= 18;```
Break values are hard-coded. Change them with substitution variables if your break values change often.

Just wondering ... how did you find out that 9, 18 and 29 are breakable values? Why are they "unique" and different from other values in your table?

3. Registered User
Join Date
Aug 2004
Location
France
Posts
754
Littlefoot, I think what webindra wanted is to break each 4 results, not on hard-coded values as you proposed.

Webindra, could you please explain what you want more precisely ?

Regards,

RBARAER

4. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,113
I admit, break on every fourth record didn't cross my mind . If that's the catch, you could try this procedure:
Code:
```CREATE OR REPLACE PROCEDURE prc_breaks (par_break_lines IN NUMBER)
IS
l_min_init TABLE1.a%TYPE;
l_max_init TABLE1.a%TYPE;
l_min_val  TABLE1.a%TYPE;
l_max_val  TABLE1.a%TYPE;
l_counter  NUMBER := 1;
BEGIN
SELECT MAX(a), MIN(a)
INTO l_min_init, l_max_init
FROM TABLE1;

l_min_val := l_min_init;
l_max_val := l_max_init;

FOR cur_r IN (SELECT a, b FROM TABLE1 ORDER BY a)
LOOP
IF cur_r.a < l_min_val THEN
l_min_val := cur_r.a;
END IF;
IF cur_r.a > l_max_val THEN
l_max_val := cur_r.a;
END IF;

IF MOD(l_counter, 4) = 3
THEN
dbms_output.put_line('Min = ' || l_min_val || ', Max = ' || l_max_val);
l_min_val := l_min_init;
l_max_val := l_max_init;
END IF;
l_counter := l_counter + 1;
END LOOP;
END prc_breaks;
/```
Execute it using "EXECUTE prc_breaks(4)" where "4" represents number of records you're breaking.

BTW, Webindra, your third result is wrong: max value isn't 29 (because it IS the third break, not BEFORE it) but 28
Last edited by Littlefoot; 01-10-05 at 09:26.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•