Forum Stats

  • 3,734,031 Users
  • 2,246,861 Discussions
  • 7,857,002 Comments

Discussions

sql query for this???

433185
433185 Member Posts: 292
edited November 2010 in SQL & PL/SQL
Hi

I have a table with data in the following format
Col1   COL2
A 2001
B
C
D
E
F 2002
G
H
I 2003
I want a query that would fill the gaps with the last non-null value in col2

desired output
Col1   COL2       output_col
A 2001 2001
B 2001
C 2001
D 2001
E 2001
F 2002 2002
G 2002
H 2002
I 2003 2003
as you can see the output_column shows the last non-null value of the col2 for each row..if for any row it finds a new col2 value then it shows that value for that row and for next rows until it finds another new one and so on


how to achive that from sql query...??

regards,

Comments

  • 438877
    438877 Member Posts: 1,849
    Supposing your years are increased sequentially by 1 and no gaps in them
    the very particular query could be:
    SQL> select * from t;

    C COL2
    - ----------
    A 2001
    B
    C
    D
    E
    F 2002
    G
    H
    I 2003

    9 rows selected.

    SQL> select col1, first_value(col2) over(order by col1) - 1 +
    2 + count(col2) over(order by col1) cl2
    3 from t
    4 /

    C CL2
    - ----------
    A 2001
    B 2001
    C 2001
    D 2001
    E 2001
    F 2002
    G 2002
    H 2002
    I 2003

    9 rows selected.
    Rgds.
  • 433185
    433185 Member Posts: 292
    edited November 2005
    dnikiforov,
    thanks for ur code
    but the problem is not solved
    the col2 values are not years they are just numbers can be any whole number
    1,2,3,......45,....567 etc and they may come in any order
    Col1    COL2
    - ----------
    A 23
    B
    C
    D
    E
    F 45
    G
    H
    I 33
    desired output
    Col1    COL2     output_col
    - ----------
    A 23 23
    B 23
    C 23
    D 23
    E 23
    F 45 45
    G 45
    H 45
    I 33 33
    what would be query for this..
    it is just a coincident that in my example they look like sequence years..

    would u pls adjust ur query
  • 438877
    438877 Member Posts: 1,849
    Not sure it's optimal solution, but somehow:
    SQL> select * from t;

    C COL2
    - ----------
    A 23
    B
    C
    D
    E
    F 45
    G
    H
    I 33

    9 rows selected.

    SQL> select col1, col2, max(col2) over(partition by sm order by col1) col2
    2 from (
    3 select col1, col2, sum(col2) over(order by col1) sm
    4 from t
    5 )
    6 /

    C COL2 COL2
    - ---------- ----------
    A 23 23
    B 23
    C 23
    D 23
    E 23
    F 45 45
    G 45
    H 45
    I 33 33

    9 rows selected.
    Rgds.
  • 452095
    452095 Member Posts: 332
    Here is an alternative way to get your desired outcome using different analytical functions:
    select  col1
    , decode(col2, null, lag(col2,lvl) over (order by col1)
    , col2) col2
    from ( select yt.*
    , level-1 lvl
    from ( select col1
    , col2
    , decode(col2, null, (lag(col1,1) over (order by col1))
    , null) fkey
    from yourtable
    ) yt
    connect by prior col1 = fkey
    start with fkey is null
    )
  • 245482
    245482 Member Posts: 1,254
    I like dnikiforov's approach, but I'd modify it to group records together with count(*) to make the goal a bit clearer. At that point either first_value() or max() will get you want you're looking for.
    create table t(col1 varchar2(8), col2 number);
    
    insert into t values('A', 23);
    insert into t values('B', null);
    insert into t values('C', null);
    insert into t values('D', null);
    insert into t values('E', null);
    insert into t values('F', 45);
    insert into t values('G', null);
    insert into t values('H', null);
    insert into t values('I', 33);
    commit;
    
    SELECT v.*,
           FIRST_VALUE(col2) OVER (PARTITION BY grp ORDER BY col1) AS first_col2,
           MAX(col2) OVER (PARTITION BY grp) AS max_col2
      FROM (
    SELECT t.*,
           COUNT(col2) OVER (ORDER BY col1) AS grp
      FROM t
           )  v;
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    create table T(
    Col1 char(1),
    Col2 number(4));
    insert into T values('A',1111);
    insert into T values('B',null);
    insert into T values('C',null);
    insert into T values('D',null);
    insert into T values('E',null);
    insert into T values('F',3333);
    insert into T values('G',null);
    insert into T values('H',null);
    insert into T values('I',2222);
    commit;
    select Col1,Col2,
    Last_Value(col2 ignore nulls) over(order by col1) as Col3
    from T;
    OracleSQLPuzzle
    http://www.geocities.jp/oraclesqlpuzzle
  • 477152
    477152 Member Posts: 137
    hi,

    why cant we use NVL ?

    select nvl(year,2001) from table_name;

    Trinath Somanchi,
    Hyderabad.
  • Rengudi
    Rengudi Member Posts: 546
    Dear Friend

    we cant use NVL here. Since, Some of the places the actual values unique. Instead of using NVL we can use Nullif.

    Regds
    Ranga
This discussion has been closed.