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

CodePudding user response：

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

CodePudding user response：

Your data seems to be entering in pairs, you can format your CreatedDateTime column for hours and minutes to create a "pair key".

I wouldn't consider this solution to be perfect though since a CreatedDateTime value can be inserted with non-matching hour and minute values (i.e. 2022-10-11 12:30 and 2022-10-11 12:31). You can possibly use your OverallRank column to create a better pair key but if I were you, I'd create a new column to use a key during your INSERT.

Once you establish a key, you can use DENSE_RANK to do the rest.

With that said, this query does suffice for your provided sample data:

 SELECT *, 
      DENSE_RANK() OVER(ORDER BY FORMAT(CreatedDateTime,'hh:mm') DESC, CustomerId, QuoteEmail) AS ExpectedOutcome 
      FROM temptable
    ORDER BY OverallQuoteRank DESC

Fiddle here.

Result: