Retrieve min and max store number that have the same location

Our "store" table looks something like this:

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:

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.

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

The other answer is much cleaner, but since I put in the time I'm posting anyway.

As already stated its a gaps-and-islands problem, you need a way to mark each group you want to analyse.

Explanation in the SQL.

create table Test (store_id int, city_id int, store_type varchar(12));
insert into Test (store_id, city_id, store_type)
values
(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');
with cte1 as (
select *
-- If the store_type or the city_id changes its a new group[
, case when store_type <> lag(store_type, 1, store_type) over (order by store_id)
or city_id <> lag(city_id, 1, city_id) over (order by store_id) then 1 else 0 end Transition
from Test
), cte2 as (
select *
-- Sum the transitions to provide a unique group id
, sum(Transition) over (order by store_id) [Grouping]
from cte1
)
select
-- Calculate desired results per group
min(store_id) min_store_id
, max(store_id) max_store_id
, city_id
, store_type
from cte2
group by
city_id
, store_type
, [Grouping]
order by min_store_id;

Returns

Note: Providing the DDL+DML as shown here makes it much easier for people to answer.

db<>fiddle