Skip to Main Content
  • Questions
  • How to get MIN and MAX of Consecutive Numbers

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: February 15, 2003 - 6:36 pm UTC

Last updated: January 15, 2018 - 2:11 pm UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Thanks for your awesome help on "Trying to split serial number ranges" question today.
I have another opportunity to ask a question and it's somewhat related to my earlier question.
I hope this would be an easy one.

I have a table with the following numbers

1
2
3
4
5
6
7
10
13
14
15
16
17
18
25
26
27
28
29
30
32
33
34

I need to have an output as shown below

1-7
10-10
13-18
25-30
32-34

Please note, that 1 through 7 are consecutive and hence I need MIN and MAX for this range of numbers. Next comes 10 and since the next number 13 is not consecutive, my MIN and MAX should both be 10 and 10 and so on.. So basically, I need to group these numbers
if they are consecutive and then get MIN and MAX for that set of numbers. I am able to viusualize this but cannot put it in SQL.

Thanks

and Tom said...

Ahh, zen and the art of SQL -- once you get into the zone, you can see it all.

Well, this is a simple variation on that first theme (glad you asked the other one first, made this one a 5 second answer!)

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7866543931332 <code>
was the first answer and this just builds on it - almost the same query (except for the addition of the cartesian product with dual that is)

ops$tkyte@ORA920> select max( decode(mod(rn,2),1,x,null) ),
2 max( decode(mod(rn,2),0,x,null) )
3 from (
4 select x, row_number() over ( order by x ) rn
5 from (
6 select *
7 from (
8 select x,
9 nvl(lag(x) over (order by x)+1,-1) last_x,
10 nvl(lead(x) over (order by x)-1,-1) next_x
11 from t
12 ), ( select 1 xx from dual
13 union all
14 select 2 xx from dual )
15 )
16 where (x <> last_x and xx = 1) OR (x <> next_x and xx = 2)
17 )
18 group by ceil(rn/2)
19 /

MAX(DECODE(MOD(RN,2),1,X,NULL)) MAX(DECODE(MOD(RN,2),0,X,NULL))
------------------------------- -------------------------------
1 7
10 10
13 18
25 30
32 34

ops$tkyte@ORA920>


I'll leave it to the jedi sql guys to explain it. just feel the "force" ;)

analytics rock and roll don't they....


The explaination is hidden in the other answer -- the cartesian product and modified where clause:

16 where (x <> last_x and xx = 1) OR (x <> next_x and xx = 2)

was just to get the even number of rows I needed for the row_number pivot to work right and get those single digit ranges working -- in fact you could add that to the other answer to get 99-99 to pop out for you.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

AWESOME

A reader, February 15, 2003 - 7:09 pm UTC

Tom,

Thanks once again. Analytic functions rock as you say. Cannot wonder but feel, that I should start looking at this chapter in your book ASAP.

Truly appreciate your help on these 2 questions :-)

Additional question

Mauro Papandrea, November 06, 2003 - 7:43 am UTC

Analytics are really powerful, however, in the very special case asked the following query seemingly gives the correct answer.
May i ask you when the analytic is superior ( for instance because the other one fails ) ?

SELECT MIN (x) , MAX (x)
FROM ( SELECT x FROM t ORDER BY x )
GROUP BY x - ROWNUM

Thanks

Best regards

Excellent

A reader, January 22, 2004 - 12:37 am UTC

Tom,
Are you by any chance a member of Mensa (High IQ people society).?

Tom Kyte
January 22, 2004 - 6:32 am UTC

nope.

Bravo Papandrea !

Pieraldo Antonello, May 04, 2004 - 10:21 am UTC

Bravo Papandrea !

Use rownum to optimize your query little bit more :)

Frank Zhou, January 13, 2005 - 5:37 pm UTC

Tom,
Thanks for sharing your valuable knowledge with all the Oracle developers and DBA.
I got all of your books. These book are excellent!!! I am looking forward to read your future new books!

I think that we can use the rownum instead of row_number() over (order by x) to improve your query performance little bit more :)

select max( decode(mod(rownum,2),1,x,null) ),
max( decode(mod(rownum,2),0,x,null) )
from ( select x
from ( select *
from (
select x,
nvl(lag(x) over (order by x)+1,-1) last_x,
nvl(lead(x) over (order by x)-1,-1) next_x
from t
), ( select 1 xx from dual
union all
select 2 xx from dual )
)
where (x <> last_x and xx = 1) OR
(x <> next_x and xx = 2)
order by x
)
group by ceil(rownum/2)


Here is my another solution to the original question :

select max( decode(mod(rownum,2),1,x,null) ),
max( decode(mod(rownum,2),0,x,null) )
from ( select * from
(select x,
count(*) over (order by x range between 1 preceding and current row) last_x,

count(*) over (order by x range between current row and 1 following) next_x from t ) ,
(select rownum xx from (select 1 from dual group by cube (1) ) )

where (last_x = 1 and xx = 1) or
( next_x = 1 and xx = 2)
order by x
)
group by ceil( rownum/2)

Thanks again for your knowledge!!

Other excellent Query soultion!!

Frank Zhou, January 14, 2005 - 4:31 pm UTC

Hi Tom,

I just found out that you have an other excellent soultion to this kind of question on your website :)


select min(x), max(x)
from (
select x,
max(grp) over (order by x) grp
from (
select x,
case when nvl(lag(x) over (order by x),x) != x-1
then x
end grp
from t
)
)
group by grp

Thanks again for your query!

Simpler answer

Ankit, January 15, 2018 - 1:36 pm UTC

Please let me your thoughts on the below query

SELECT MIN(col1) start_no,
MAX(col1) end_no
FROM (SELECT col1,

col1 - ROW_NUMBER()
over(
ORDER BY col1) rn
FROM test
)
GROUP BY rn
order by 1
Chris Saxon
January 15, 2018 - 2:11 pm UTC

Yep, that works too. It's known as the Tabibitosan method

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.