Problem solve Get help with specific problems with your technologies, process and projects.

Runs in groups

Rudy Limeback addresses the classic "runs" problem in SQL.

We have a table as below:

create table goods (
  barcode  number(15,0) not null primary key,
  category number(15,0) not null,
  num      number(15,0) not null,
  mark     varchar2(10) not null )

The data of the table as below:

barcode  category   num  mark
9679839  100003894  212   E
9687165  100003894  213   E
9680883  100003894  214   I
9710863  100003894  515   E
9681246  100003894  516   E
9682695  100003894  517   E
9681239  100003894  518   E
9685409  100003894  519   E
9679843  100003894  520   C
9679844  100003894  521   C
9714882  100003894  522   E
9679845  100003894  523   I
9681211  100003894  524   E
9681216  100003894  525   E

The expected query result is as follows:

category  mark  start end         
100003894  E    212   213
100003894  I    214      
100003894  E    515   519
100003894  C    520   521
100003894  E    522      
100003894  I    523      
100003894  E    524   525


This is the classic "runs" problem.

select category
     , mark
     , start
     , case when start = end 
            then null
            else end end as end
  from (
select dt.category
     , dt.mark
     , min(dt.num) as start
     , max(dt.num) as end
  from (
       select s1.category
            , s1.mark
            , s1.num
            , s1.num -
              ( select count(*)
                  from goods as s2
                 where s2.category = s1.category
                   and s2.mark     = s1.mark
                   and s2.num     <= s1.num )
               as diff_rank
        from goods as s1
      ) as dt
    by dt.category
     , dt.mark
     , dt.diff_rank
) as dt2
    by category
     , start

For the solution, I am indebted to Joe Celko's SQL for Smarties, 3rd edition, page 556. Please do not ask me to explain it. Instead, test it yourself, by building up your query starting at the DT subquery. Analyze the results carefully. The outermost query is merely for cosmetic purposes.

Incidentally, this is the first time I can recall ever writing ELSE END END AS END in SQL. This may not work in every database system, so use identifier delimiters as necessary.

This was last published in February 2007

Dig Deeper on Oracle and SQL



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.