Table mytbl have two columns: col_1 & col_2. I wanted to group range of values in col_1 for single col_2 value.
Example:
| col_1 | col_2 |
|---|---|
| 1 | 3 |
| 2 | 1 |
| 3 | 3 |
| 4 | 3 |
| 5 | 2 |
| 7 | 3 |
| 8 | 3 |
| 9 | 3 |
| 10 | 1 |
I wanted to filter out the a range for col_2 = 3.
it shows hiphen(-) for the end value when there is no range.
Result:
| start | end | col_2 |
|---|---|---|
| 1 | - | 3 |
| 3 | 4 | 3 |
| 7 | 9 | 3 |
This is a gaps and island problem. Here is an approach using the difference between row numbers to identify the groups:
select
min(col_1) as start_col_1,
case when max(col_1) <> min(col_1) then max(col_1) end as end_col_1,
col2
from (
select t.*,
row_number() over(partition by col2 order by col_1) as rn
from mytable t
) t
where col_2 = 3
group by col2, col_1 - rn
order by start_col1
This returns null rather than '-' when the islands is made of just one record (that is because the latter is not a valid number).
This works as long as col_1 increments without gaps. Else, we can generate our own sequence with another row_number():
select
min(col_1) as start_col_1,
case when max(col_1) <> min(col_1) then max(col_1) end as end_col_1,
col2
from (
select t.*,
row_number() over(order by col_1) as rn1,
row_number() over(partition by col2 order by col_1) as rn2
from mytable t
) t
where col_2 = 3
group by col2, rn1 - rn2
order by start_col1
