Rank based on sequence of dates

Question

I am having data as below

**Heading    Date**
A          2009-02-01
B          2009-02-03
c          2009-02-05
d          2009-02-06
e          2009-02-08

I need rank as below

Heading    Date           Rank
A          2009-02-01      1
B          2009-02-03      2
c          2009-02-05      1
d          2009-02-06      2
e          2009-02-07      3

As I need rank based on date. If the date is continuous the rank should be 1, 2, 3 etc. If there is any break on dates I need to start over with 1, 2, ...

Can any one help me on this?

Answer 1

SELECT heading, thedate
      ,row_number() OVER (PARTITION BY grp ORDER BY thedate) AS rn
FROM  (
   SELECT *, thedate - (row_number() OVER (ORDER BY thedate))::int AS grp
   FROM   demo
   ) sub;

While you speak of "rank" you seem to want the result of the window function row_number().

1. Form groups of consecutive days (same date in grp) in subquery sub.
2. Number rows with another row_number() call, this time partitioned by grp.

One subquery is the bare minimum here, since window functions cannot be nested.

SQL Fiddle.

Note that I went with the second version of your contradictory sample data. And the result is as @mu suggested in his comment.
Also assuming that there are no duplicate dates. You'd have to aggregate first in this case.