title: Create Column in sql based on condition?
body: <p>table:</p>
  
  <pre><code>  Student_Id    Roll_No    Date   _Rank
  1    101         10101    201905    3
  2    101         10101    201910    6
  3    101         10101    201912    9
  4    101         10102    201905    15
  5    101         10102    201910    9
  6    101         10102    201912    3
  7    103         10103    201905    3
  8    103         10103    201910    9
  9    103         10103    201912    9
  10   103         10104    201905    3
  11   103         10104    201910    9
  12   103         10104    201912    9
  </code></pre>
  
  <p>i need to create a result column based on the condition.</p>
  
  <ol>
  <li><p>check for particular Student_Id,Roll_No if rank is 9 and rank> 9 has not 
  appeared before 9 as per date then 1 else 0.</p></li>
  <li><p>if some Student_Id,Roll_No has multiple 9 then flag only the first occurrence.</p></li>
  </ol>
  
  <p>Result:</p>
  
  <pre><code>  Student_Id    Roll_No    Date   _Rank   Result
  1    101         10101    201905    3       0
  2    101         10101    201910    6       0
  3    101         10101    201912    9       1 
  4    101         10102    201905    15      0 ---
  5    101         10102    201910    9       0 --- 15 &gt; 9, came before 9 for that Roll_No
  6    101         10102    201912    3       0
  7    103         10105    201905    9       1
  8    103         10103    201910    9       1
  9    103         10103    201912    9       0
  10   103         10104    201905    3       0
  11   103         10104    201910    9       1 --- first occurrence should 1
  12   103         10104    201912    9       0
  </code></pre>
  
  <p>Query:</p>
  
  <pre><code>SELECT Student_Id, Roll_Number, Date, _Rank,
  ROW_NUMBER() OVER( PARTITION BY Roll_Number ORDER BY Date) as rw_number
  FROM table t
  -- this is giving the row_number which is appeared first.
  </code></pre>