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.numHere are the resulting rows:
Ranges ------- 1 - 5 7 10 - 12
For More Information
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in November 2002