CODEWITHSUNDEEP
sqloracle-database
mallikarjun
mallikarjun

Reputation: 1862

Oracle query to display count and start, end of sequence

I know basics of Oracle and I am a java developer, I can do the following operation/task in java by fetching the data and iterate over it. But I would like to know that is there any way to show start and end of the sequence and the difference in between start and end using SQL(Oracle) query.

Let's say I have a table TB1 with a column seq which contains some sequential numbers

 SEQ
------
1
2
3
7
8
9
14
19
20

Is there any way to display the sequence start, end and the count as follows.

Start  | end | count
---------------------
1        3     3
7        9     3
14       14    1
19       20    2

Please give me some pointer if it is achievable or not. Thanks in advance.

Upvotes: 1

Views: 1165

Answers (3)

San
San

Reputation: 4538

SQL> WITH cte_table (seq) AS (
  2    SELECT 1 FROM dual UNION ALL
  3    SELECT 2 FROM dual UNION ALL
  4    SELECT 3 FROM dual UNION ALL
  5    SELECT 7 FROM dual UNION ALL
  6    SELECT 8 FROM dual UNION ALL
  7    SELECT 9 FROM dual UNION ALL
  8    SELECT 14 FROM dual UNION ALL
  9    SELECT 19 FROM dual UNION ALL
 10    SELECT 20 FROM dual),
 11  table_ AS (
 12  SELECT seq, seq - row_number() OVER (ORDER BY seq) grp FROM cte_table)
 13  SELECT MIN(seq) "START",
 14         MAX(seq) "END",
 15         COUNT(*) "COUNT"
 16    FROM table_
 17   GROUP BY grp
 18   ORDER BY 1;

Output:

     START        END      COUNT
---------- ---------- ----------
         1          3          3
         7          9          3
        14         14          1
        19         20          2

Using your table, the query will be

WITH table_ AS (
  SELECT seq, seq - row_number() OVER (ORDER BY seq) grp FROM tb1) 
SELECT MIN(seq) "START",
       MAX(seq) "END",
       COUNT(*) "COUNT"
  FROM table_
 GROUP BY grp
 ORDER BY 1;

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

Yes. You could do it easily using TABIBITOSAN method.

SELECT MIN(seq)
    ,MAX(seq)
    ,count(*)
FROM (
    SELECT seq
        ,seq - row_number() OVER (
            ORDER BY seq
            ) grp
    FROM t
    )
GROUP BY grp
ORDER BY 1;

Demo

Upvotes: 4

Red Boy
Red Boy

Reputation: 5739

You should write a procedure, and loop in through cursor. Keep three temp variable, wherever there is break in seq(make sure you do Order by on Seq column), and insert the start,end and count into other table.

See similar example that may be helpful to you.

Upvotes: 0

Related Questions