CODEWITHSUNDEEP
mysqlsqlwindow-functionsgaps-and-islands
Zen
Zen

Reputation: 21

get the range of sequence values in table column

I have a list of value in my column. And want to query the range. Eg. If values are 1,2,3,4,5,9,11,12,13,14,17,18,19

I want to display 1-5,9,11-14,17-19

Upvotes: 1

Views: 433

Answers (2)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

As a complement to other answers:

select dn.val as dnval, min(up.val) as upval 
from mytable up
join mytable dn
    on dn.val <= up.val
where not exists (select 1 from mytable a where a.val = up.val + 1)
  and not exists (select 1 from mytable b where b.val = dn.val - 1)
group by dn.val
order by dn.val;

1   5
9   9
11  14
17  19

Needless to say, but using an OLAP function like @GNB does, is orders of magnitude more efficient.

A short article on how to mimic OLAP functions in MySQL < 8 can be found at:

mysql-row_number

Fiddle

EDIT:

If another dimension is introduced (in this case p), something like:

select dn.p, dn.val as dnval, min(up.val) as upval 
from mytable up
join mytable dn
    on dn.val <= up.val
    and dn.p = up.p
where not exists (select 1 from mytable a where a.val = up.val + 1 and a.p = up.p)
  and not exists (select 1 from mytable b where b.val = dn.val - 1 and b.p = dn.p)
group by dn.p, dn.val
order by dn.p, dn.val;

can be used, see Fiddle2

Upvotes: 0

GMB
GMB

Reputation: 222462

Assuming that each value is stored on a separate row, you can use some gaps-and-island technique here:

select case when min(val) <> max(val)
    then concat(min(val), '-', max(val))
    else min(val)
end val_range
from (select val, row_number() over(order by val) rn from mytable) t
group by val - rn
order by min(val)

The idea is to build groups of consecutive values by taking the difference between the value and an incrementing rank, which is computed using row_number() (available in MySQL 8.0):

Demo on DB Fiddle:

| val_range |
| :-------- |
| 1-5       |
| 9         |
| 11-14     |
| 17-19     |

In earlier versions, you can emulate row_number() with a correlated subquery, or a user variable. The second option goes like:

select case when min(val) <> max(val)
    then concat(min(val), '-', max(val))
    else min(val)
end val_range
from (select @rn := 0) x
cross join (
    select val, @rn := @rn + 1 rn 
    from (select val from mytable order by val) t
) t
group by val - rn
order by min(val)

Upvotes: 4

Related Questions