sql - How to keep the value from previous records -

my original table 't1' looks this:

     id  date        order_ind   var2   var3       1  1/1/2015            1  .....  .....       1  1/5/2015            1  .....  .....       1  1/5/2015            2  .....  .....       2  1/10/2015           1  .....  .....       2  1/20/2015           1  .....  .....       2  1/20/2015           2  .....  .....       2  1/20/2015           3  .....  ..... 

the final table want create adding additional variable 'new_var' based on criteria. may notice, there records same date, , criteria work on first record (order_ind=1). rest of records same date, such order_ind=2, or 3, new_var value should same order_ind=1 record.

     id  date        order_ind   var1   var2    new_var          1  1/1/2015            1  .....  .....    1       1  1/5/2015            1  .....  .....    0       1  1/5/2015            2  .....  .....    0       2  1/10/2015           1  .....  .....    0       2  1/20/2015           1  .....  .....    1       2  1/20/2015           2  .....  .....    1       2  1/20/2015           3  .....  .....    1 

the sql codes wrote these:

     select *,             case             when order_ind=1 , (criteria1....) '1'             when order_ind=1 , (criteria2....) '0'              when order_ind<>1 .......(please advise how code this)              end new_var      t1      ; 

any idea how write code records order_ind<>1?

i in few passes. first, make ind_1_new_var column contains values order_ind = 1 records.

select   *   ,case     when order_ind = 1 , (criteria1...) 1     when order_ind = 1 , (criteria2...) 0     else null   end ind_1_new_var   t1; 

then build new_var referencing column.

select   *   ,case     when order_ind = 1 , (criteria1...) 1     when order_ind = 1 , (criteria2...) 0     else null   end ind_1_new_var   ,max(ind_1_new_var) on (     partition id, date   ) new_var   t1; 

i don't know criteria1, here's working example in nz database data gave.

test_db(admin)=> select * t1 order 1,2,3;  id |  t1_date   | order_ind | var1 | var2 ----+------------+-----------+------+------   1 | 2015-01-01 |         1 |    0 |    0   1 | 2015-01-05 |         1 |    0 |    0   1 | 2015-01-05 |         2 |    0 |    0   2 | 2015-01-10 |         1 |    0 |    0   2 | 2015-01-20 |         1 |    0 |    0   2 | 2015-01-20 |         2 |    0 |    0   2 | 2015-01-20 |         3 |    0 |    0 (7 rows)  test_db(admin)=> select test_db(admin)->   * test_db(admin)->   ,case test_db(admin)->     when order_ind = 1 , ( test_db(admin)(>       (id = 1 , t1_date = '2015-01-01') test_db(admin)(>       or (id = 2 , t1_date = '2015-01-20') test_db(admin)(>     ) 1 test_db(admin)->     when order_ind = 1 , ( test_db(admin)(>       (id = 1 , t1_date = '2015-01-05') test_db(admin)(>       or (id = 2 , t1_date = '2015-01-10') test_db(admin)(>     ) 0 test_db(admin)->     else null test_db(admin)->   end ind_1_new_var test_db(admin)->   ,max(ind_1_new_var) on ( test_db(admin)(>     partition id, t1_date test_db(admin)(>   ) new_var test_db(admin)-> test_db(admin)->   t1 test_db(admin)-> order 1,2,3;  id |  t1_date   | order_ind | var1 | var2 | ind_1_new_var | new_var ----+------------+-----------+------+------+---------------+---------   1 | 2015-01-01 |         1 |    0 |    0 |             1 |       1   1 | 2015-01-05 |         1 |    0 |    0 |             0 |       0   1 | 2015-01-05 |         2 |    0 |    0 |               |       0   2 | 2015-01-10 |         1 |    0 |    0 |             0 |       0   2 | 2015-01-20 |         1 |    0 |    0 |             1 |       1   2 | 2015-01-20 |         2 |    0 |    0 |               |       1   2 | 2015-01-20 |         3 |    0 |    0 |               |       1 (7 rows)