Reader Paul Moore of Atos Origin S.A. was kind enough to respond with an analytic solution to the problem posed in a previous answer, Counting rows in runs. His solution follows.
Assume a table called runs, defined as follows:
create table runs ( id number , category varchar2(1) ); insert into runs values (1, 'X'); insert into runs values (2, 'X'); insert into runs values (3, 'X'); insert into runs values (4, 'Y'); insert into runs values (5, 'Y'); insert into runs values (6, 'X'); insert into runs values (7, 'X'); commit;
The following works:
select block, category, count(*) from ( select id, category, sum(prev_cat) over (order by id rows unbounded preceding) block from ( select id, category, case when (category = lag(category) over (order by id) ) then 0 else 1 end prev_cat from runs ) ) group by block, category order by block BLOCK C COUNT(*) ----- - -------- 1 X 3 2 Y 2 3 X 2
The trick is to work out from the inner inline view. First, use "lag" to identify when the category changes. You can use decode to get an Oracle 8i solution -- I used the 9i case operator. You also need to watch out for the first row, where lag() returns null. Then, sum the number of category changes which occur "before" a given row - that is its "block" (ie, the sequence number of the run it's in). Then you just count and group by the block number.
Hope this is of interest.
Yes, it is. Thanks very much, Paul.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.