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.
2条答案
按热度按时间rt4zxlrg1#
You have a gaps and islands problem here, you could use the difference between two row_numbers approach to create groups :
Result :
Demo here
6rqinv9w2#
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.
Returns
Note: Providing the DDL+DML as shown here makes it much easier for people to answer.
db<>fiddle