Suppose I have Table named emp like this
| empno | comm |
| 033001 | RUET |
| 033002 | |
| 033003 | |
| 033004 | KUET |
| 033004 | |
| 033005 | CUET |
Now I want to fill the blank cells with the previous value. Such as comm of 033002, 033003 will be RUET. comm of 033004 will be KUET. How can I do this? I had googled and found an interesting query.
1. I have to execute following query:
select empno,comm, lag(comm ignore nulls) over (order by empno)prev_comm from emp order by empno.
Then I get a view like this :
| empno | comm | prev_comm |
| 033001 | RUET | |
| 033002 | RUET | |
| 033003 | RUET | |
| 033004 | KUET | RUET |
| 033004 | KUET | |
| 033005 | CUET | KUET |
| CUET |
2. Suppose the previous view is represented by mm.
Then I have to write the following query on mm view.
select empno, case when comm is null then prev_comm else comm end as comm from mm.
Then we will get the desired view :
| empno | comm |
| 033001 | RUET |
| 033002 | RUET |
| 033003 | RUET |
| 033004 | KUET |
| 033004 | KUET |
| 033005 | CUET |
So, the final query will be:
select empno, case when comm is null then prev_comm else comm end as comm from (select empno,comm, lag(comm ignore nulls) over (order by empno)prev_comm from emp order by empno)
