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!

Need help on getting first max, value max and so on

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Jul 12, 2017.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi,
    I have a table with column two columns

    Ex: Let us consider the two columns as below:
    Col1 Col2
    1 A
    2
    3
    4 D
    5

    My requirement is to get

    Col1 Col2
    1 A
    2 A
    3 A
    4 D
    5 D

    That is to update Col2 where it is null with the previous non null value as shown.

    Here it is only 5 records but it can vary


    Kindly help me with the query
     
  2. naveen.lazaus

    naveen.lazaus Active Member

    Messages:
    6
    Likes Received:
    2
    Trophy Points:
    85
    Hi, Try this and let me know

    select cl,decode(cl1,'A','A'
    ,null,Lag(cl1) OVER (ORDER BY cl)
    ,'D','D') from t_ex1;
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    733
    Likes Received:
    143
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    For example :
    Code (Text):

    WITH tab1 (Col1,Col2) AS
    (SELECT 1 ,'A' FROM dual UNION ALL
     SELECT 2,to_char(NULL) FROM dual UNION ALL
     SELECT 3,to_char(NULL) FROM dual UNION ALL
      SELECT 4,'D' FROM dual UNION ALL
     SELECT 5,to_char(NULL) FROM dual
     )
    ,tab2 AS (
    SELECT
          col1,
          col2,
          sum(NVL2(col2,1,0)) OVER( ORDER BY col1) gr   -- this determinating of  group number
    FROM tab1
    )
    SELECT t.col1,t.col2,
        -- here ,may be a different analytical function
        first_value(t.col2) OVER(PARTITION BY t.gr ORDER BY t.col1) new_col2
    FROM tab2 t;
     
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,531
    Likes Received:
    360
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Had you tested this before posting it you would have found it does not work as you expect:

    Code (SQL):
    SQL> SELECT cl,decode(cl1,'A','A'
      2  ,NULL,Lag(cl1) OVER (ORDER BY cl)
      3  ,'D','D') FROM t_ex1;

            CL DE
    ---------- --
             1 A
             2 A
             3
             4 D
             5 D

    SQL>
    You are relying on a condition that cannot exist, namely that the prior value for cl1 will be 'A' after the processing starts and that is wrong. To get where you thought you were with the first pass you need to do this:

    Code (SQL):
    SQL> SELECT cl, decode(cl1,'A','A',NULL,Lag(cl1) OVER (ORDER BY cl),'D','D')
      2  FROM
      3  (SELECT cl,decode(cl1,'A','A',NULL,Lag(cl1) OVER (ORDER BY cl),'D','D') cl1
      4  FROM t_ex1) t_ex2;

            CL DE
    ---------- --
             1 A
             2 A
             3 A
             4 D
             5 D

    SQL>
    It is poor practice to expect others to test your work.