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!

Retrieve last non-blank value in the column

Discussion in 'SQL PL/SQL' started by ygsunilkumar, Jun 8, 2009.

  1. ygsunilkumar

    ygsunilkumar Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    I need SQL Function or Query wherein it will retrieve the last non-blank value in the column. In the below mentioned case, I need to retrieve value 7 of col_3. How to achieve it? Thanks in advance

    Code (SQL):


    CREATE TABLE test_211 (col_1 NUMBER, col_2 NUMBER,col_3 NUMBER);

    INSERT INTO test_211 VALUES (1,1,4);
    INSERT INTO test_211 VALUES (1,2,NULL);
    INSERT INTO test_211 VALUES (1,3,NULL);
    INSERT INTO test_211 VALUES (1,4,NULL);
    INSERT INTO test_211 VALUES (1,5,5);
    INSERT INTO test_211 VALUES (1,6,NULL);
    INSERT INTO test_211 VALUES (2,1,2);
    INSERT INTO test_211 VALUES (2,2,NULL);
    INSERT INTO test_211 VALUES (2,3,7);
    INSERT INTO test_211 VALUES (2,4,NULL);
    INSERT INTO test_211 VALUES (3,5,NULL);
    INSERT INTO test_211 VALUES (3,6,NULL);
    INSERT INTO test_211 VALUES (4,1,NULL);
    INSERT INTO test_211 VALUES (4,2,NULL);
    INSERT INTO test_211 VALUES (4,3,NULL);


    [SIZE="5"] OUTPUT [/SIZE]


    SELECT col_1 ,col_2, col_3
    FROM test_211;

     COL_1      COL_2      COL_3    
    --------- ---------- ----------
            1          1          4          
            1          2                    
            1          3                    
            1          4                    
            1          5          5          
            1          6                    
            2          1          2          
            2          2                    
            2          3          7          
            2          4                    
            3          5
            3          6
            4          1
            4          2
            4          3

       
     
     
  2. sashraf

    sashraf Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Hyderbad. India
    Code (SQL):
    SELECT   col_1, col_2, col_3
        FROM (SELECT ROWNUM AS ID, col_1, col_2, col_3
                FROM test_211
               WHERE col_3 IS NOT NULL)
       WHERE ROWNUM < 2
    ORDER BY 1 DESC
    ~Ashraf
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    sashraf's query may give you correct result now, but may be proved wrong on some updation or deletion .
     
  4. sashraf

    sashraf Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Hyderbad. India
    Hi Raj,

    I would love to see if you can you help him with a query which will give exact result for all DML's. This way i can improve myself.

    ~Ashraf
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    What I was trying to say that .. ..

    On perfoming some DML your query may give some other results . ie, rownum coulumn may not be precise always..
     
  6. krithika@2001

    krithika@2001 Active Member

    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    80
    Hi

    I am not able to follow the logic and rownum concept.Can you please help in explaining the code and how it works


    Regards
    krithika