min/max number by group, with recurring group criteria

I'm trying to create an address directory where

for each street_key
there is one row containing the address_no_start and address_no_end
until a change happens in the region variables

The problem occurs when a street_key re-enters to its previous region leading to wrong results using a very basic "group by/min-max" approach

The results show 2 rows with start 1 and end 13 becuase the street returns to REGION1 003 after with ADRESS_NO 11

DECLARE @adressInRegion AS TABLE (
ADRESS_KEY CHAR(5),
ADRESS_NO INT,
REGION1 CHAR(3),
REGION2 CHAR(2),
REGION3 CHAR(5),
REGION4 CHAR(2)
)


INSERT INTO @adressInRegion 
VALUES
('12345',1,'003','02','55555','12'),
('12345',3,'003','02','55555','12'),
('12345',5,'003','02','55555','12'),
('12345',7,'005','02','55555','12'),
('12345',9,'005','02','55555','12'),
('12345',11,'003','02','55555','12'),
('12345',13,'003','02','55555','12')


SELECT 
  ADRESS_KEY, 
  MIN(ADRESS_NO) AS ADRESS_NO_START,
  MAX(ADRESS_NO) AS ADRESS_NO_END,
  REGION1,
  REGION2,
  REGION3,
  REGION4
FROM @adressInRegion
GROUP BY
  ADRESS_KEY, 
  REGION1,
  REGION2,
  REGION3,
  REGION4

outcome:

Desired outcome:

Solution

You can use a little trick, numbering you rows according to you group key, and adding it to a descending row number.

SELECT
        ADRESS_KEY
       ,ADRESS_NO
       ,REGION1
       ,REGION2
       ,REGION3
       ,REGION4
       ,ROW_NUMBER() OVER (PARTITION BY ADRESS_KEY, REGION1, REGION2, REGION4, REGION4 ORDER BY ADRESS_NO) +
        ROW_NUMBER() OVER (PARTITION BY ADRESS_KEY ORDER BY ADRESS_NO DESC) grp
    FROM @adressInRegion

This gives you

+------------+-----------+---------+---------+---------+---------+-----+
| ADRESS_KEY | ADRESS_NO | REGION1 | REGION2 | REGION3 | REGION4 | grp |
+------------+-----------+---------+---------+---------+---------+-----+
|      12345 |         1 |     003 |      02 |   55555 |      12 |   8 |
|      12345 |         3 |     003 |      02 |   55555 |      12 |   8 |
|      12345 |         5 |     003 |      02 |   55555 |      12 |   8 |
|      12345 |         7 |     005 |      02 |   55555 |      12 |   5 |
|      12345 |         9 |     005 |      02 |   55555 |      12 |   5 |
|      12345 |        11 |     003 |      02 |   55555 |      12 |   6 |
|      12345 |        13 |     003 |      02 |   55555 |      12 |   6 |
+------------+-----------+---------+---------+---------+---------+-----+

Now you can do max min on each group. Here it's done via a CTE:

;WITH cte
AS
(SELECT
        ADRESS_KEY
       ,ADRESS_NO
       ,REGION1
       ,REGION2
       ,REGION3
       ,REGION4
       ,ROW_NUMBER() OVER (PARTITION BY ADRESS_KEY, REGION1, REGION2, REGION4, REGION4 ORDER BY ADRESS_NO) +
        ROW_NUMBER() OVER (PARTITION BY ADRESS_KEY ORDER BY ADRESS_NO DESC) grp
    FROM @adressInRegion)
SELECT
    ADRESS_KEY
   ,MIN(ADRESS_NO) ADRESS_NO_START
   ,MAX(ADRESS_NO) ADRESS_NO_END
   ,REGION1
   ,REGION2
   ,REGION3
   ,REGION4
FROM cte
GROUP BY ADRESS_KEY
        ,REGION1
        ,REGION2
        ,REGION3
        ,REGION4
        ,grp
ORDER BY ADRESS_KEY,ADRESS_NO_START

Result:

+------------+-----------------+---------------+---------+---------+---------+---------+
| ADRESS_KEY | ADRESS_NO_START | ADRESS_NO_END | REGION1 | REGION2 | REGION3 | REGION4 |
+------------+-----------------+---------------+---------+---------+---------+---------+
|      12345 |               1 |             5 |     003 |      02 |   55555 |      12 |
|      12345 |               7 |             9 |     005 |      02 |   55555 |      12 |
|      12345 |              11 |            13 |     003 |      02 |   55555 |      12 |
+------------+-----------------+---------------+---------+---------+---------+---------+