Rank a subset of data containing duplicates

Question

I am trying to rank the following subset of data;

To achieve a rank of row 1, for the first two rows, so that it outputs similar to the below;

Here is the SQL fiddle example;

http://sqlfiddle.com/#!18/f7690e/2

Answer 1

It seems like a classic gaps-and-islands problem.

You want to rank each island of emails by date. A common, if not particularly efficient, solution is to use LAG to check the previous row, then use a windowed conditional COUNT to create the numbering.

SELECT
  CustomerId,
  QuoteEmail,
  CreatedDateTime,
  OverallQuoteRank,
  COUNT(CASE WHEN PrevEmail = QuoteEmail THEN NULL ELSE 1 END) OVER (PARTITION BY CustomerId ORDER BY CreatedDateTime DESC)
FROM (
    SELECT *,
      PrevEmail = LAG(QuoteEmail) OVER (PARTITION BY CustomerId ORDER BY CreatedDateTime DESC)
    FROM temptable tt
) tt;

SQL Fiddle