Retrieve min and max store number that have the same location

Question

Our "store" table looks something like this:

store_id	city_id	store_type
1	1	regular
2	1	regular
3	1	regular
50	1	regular
51	1	express
55	1	express
58	1	express
70	1	express
71	2	regular
75	2	regular
78	2	regular
80	2	regular
85	2	regular
90	2	regular
91	1	regular
95	1	regular
97	1	regular
100	1	regular
105	1	regular

I want to create a list in SQL Server that have the same value on 2 columns so in our table store we can make a select that looks something like:

min_store_id	max_store_id	city_id	store_type
1	50	1	regular
51	70	1	express
71	90	2	regular
91	105	1	regular

However the problem is, we hade a bad store_id system so the fact that the last and first row do have the same value is something that will happen in the table, and we sadly cannot change it.

Tried with something like this:

SELECT MIN(store_id) OVER (PARTITION BY city_id, store_type) AS min_store_id,
       MAX(store_id) OVER (PARTITION BY city_id, store_type) AS max_store_id,
       city_id,
       store_type
FROM store;

but it does not work at all.

Answer 1

You have a gaps and islands problem here, you could use the difference between two row_numbers approach to create groups :

with cte as (
  select *, row_number() over (order by store_id) 
          - row_number() over (partition by city_id, store_type order by store_id) as grp
  from store
)
SELECT MIN(store_id) AS min_store_id,
       MAX(store_id) AS max_store_id,
       max(city_id) as city_id,
       max(store_type) as store_type
FROM cte
group by grp

Result :

min_store_id    max_store_id    city_id store_type
1               50              1       regular
51              70              1       express
71              90              2       regular
91              105             1       regular

Demo here