I've a data in this format.

```
    ID Group    Flag
    1    A        Y
    2    A        Y
    3    A        Y
    4    A        Y
    5    A        N
    6    A        N
    7    A        N
    8    B        N
    9    B        Y
   10    B        Y
   11    B        Y
   12    B        N
   13    B        N
   14    B        N

```

I want to get the ranking of the variables in the following order.

```
  ID Group      Flag    Rank
  1    A         Y       1
  2    A         Y       1
  3    A         Y       1
  4    A         Y       1
  5    A         N       0
  6    A         N       0
  7    A         N       0
  8    B         N       0
  9    B         Y       2
 10    B         Y       2
 11    B         Y       2
 12    B         N       0
 13    B         Y       3
 14    B         Y       3

```

Can anyone please tell me how to get this result?

**summary of why particular order in above result:**  

 I want to take max of the values for each group where Flag = "Y". The values will be selected only if they are coming continuously. For example, in case of B, I'll get 2 max values instead of 1. So, the idea is to differentiate the flags within each group

**Test data:** 

```
CREATE TABLE #TEST
(
GROUPP VARCHAR(2),
fLAG VARCHAR(2)
)

INSERT INTO #TEST
SELECT    'A',       'Y' UNION ALL
SELECT    'A',       'Y' UNION ALL
SELECT    'A',       'Y' UNION ALL
SELECT    'A',       'Y' UNION ALL
SELECT    'A',       'N' UNION ALL
SELECT    'A',       'N' UNION ALL
SELECT    'A',       'N' UNION ALL
SELECT    'B',       'N' UNION ALL
SELECT    'B',       'Y' UNION ALL
SELECT    'B',       'Y' UNION ALL
SELECT    'B',       'Y' UNION ALL
SELECT    'B',       'N' UNION ALL
SELECT    'B',       'N' UNION ALL
SELECT    'B',       'N' 

```