|
My solution to this problem may be a little difficult to follow, so I'll try to explain. My approach here was to return a set of ranges. The WHERE clause eliminates all of the numbers but those that would be the first in a range, our range "starters". However, the case statement here does all the real work. The WHEN clause checks to see if there exists a number right after our starter. If so, then we know it is truly a range, and not just a single number by itself, like the number 7 in the example. In this case, we return the starter number, concatenated with a dash to indicate a sequence of numbers, and then concatenated with a sub-query. This sub-query returns the lowest number in our set greater than our starter, that does not have a number immediately after it, our "finisher". Our stand-alone numbers, like 7, are dealt with in the CASE's ELSE clause, and simply displayed.
select case
when exists (select * from nums t where t.num = n.num + 1)
then cast (num as varchar(10)) || ' - ' ||
( select min(t1.num) from nums t1
where t1.num > n.num
and not exists ( select * from nums t2
where t2.num = t1.num + 1
)
)
else cast (num as varchar(10))
end as "Ranges"
from nums n
where not exists (select * from nums t3 where t3.num = n.num - 1)
order by n.num
Here are the resulting rows:
Ranges
-------
1 - 5
7
10 - 12
For More Information
|
