1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Question on max and count function

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Oct 3, 2016.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    How to get max of a column value and also check to see if all the values in that column are the same.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You really need to read through the online Oracle documentation for the version you are using; here is one way to do what you ask:

    Code (SQL):
    SQL> CREATE TABLE all_vals_same(
      2  a       NUMBER,
      3  b       NUMBER,
      4  c       varchar2(40));

    TABLE created.

    SQL>
    SQL> BEGIN
      2          FOR i IN 1..100 loop
      3                  INSERT INTO all_vals_same
      4                  VALUES(i,10,'Record '||i);
      5          END loop;
      6
      7          commit;
      8  END;
      9  /

    PL/SQL PROCEDURE successfully completed.

    SQL>
    SQL> SELECT MAX(b)
      2  FROM all_vals_same;

        MAX(B)
    ----------
            10

    SQL>
    SQL> SELECT MIN(b)
      2  FROM all_vals_same;

        MIN(B)
    ----------
            10

    SQL>
    SQL> SELECT b, next_b
      2  FROM
      3  (SELECT b, lead(b) OVER (ORDER BY b) next_b
      4  FROM all_vals_same)
      5  WHERE next_b <> b;

    no ROWS selected

    SQL> INSERT INTO all_vals_same
      2  VALUES(101,100, 'Record 101');

    1 ROW created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> SELECT MAX(b)
      2  FROM all_vals_same;

        MAX(B)
    ----------
           100

    SQL>
    SQL> SELECT MIN(b)
      2  FROM all_vals_same;

        MIN(B)
    ----------
            10

    SQL>
    SQL> SELECT b, next_b
      2  FROM
      3  (SELECT b, lead(b) OVER (ORDER BY b) next_b
      4  FROM all_vals_same)
      5  WHERE next_b <> b;

             B     NEXT_B
    ---------- ----------
            10        100

    SQL>
     
    The last query walks the values of B and compares the current value with the next available value; when the next value is not equal to the current value that row will be returned, which you see when the last record was added that had a different value for B.
     
  3. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Thank you very much for your reply.