Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Exclamation 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....
    Thanks in advance
    Indranil

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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. #3
    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. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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
  •