Rank consecutive null values
<p>I want to rank consecutive null value for my records. Every record will be rank as 1. For the null value that only appear once, the rank will also be 1. But for the null values that appear in a consecutive way, the rank will be 1 for the first record and 2 for the second record and so on. Here's my code.</p>

<pre><code>CREATE TABLE #my_table
(
    id BIGINT                      IDENTITY PRIMARY KEY
    ,fruit                          varchar(100)
);

INSERT INTO #my_table
          SELECT 'apple'
UNION ALL SELECT 'apple'
UNION ALL SELECT NULL
UNION ALL SELECT 'pineapple'
UNION ALL SELECT 'banana'
UNION ALL SELECT  NULL
UNION ALL SELECT  NULL
UNION ALL SELECT 'orange'

select * from #my_table
</code></pre>

<p><strong>Intended result</strong></p>

<pre><code>+----+-----------+------+
| id | fruit     | rank |
+----+-----------+------+
|  1 | apple     | 1    |
|  2 | apple     | 1    |
|  3 | NULL      | 1    |
|  4 | pineapple | 1    |
|  5 | banana    | 1    |
|  6 | NULL      | 1    |
|  7 | NULL      | 2    |
|  8 | orange    | 1    |
+----+-----------+------+
</code></pre>

<p>How should I query it?</p>

<p>Please help!</p>