I have table with below data :
1 5
2 5
3 5
4 0
5 0
6 4
7 4
And I have to write query that give me below output :
Block_id
1-3
6-7
How to achieve this with pl/sql in Oracle 11g ?
you can do something like:
SQL> select * from data order by block_id;
BLOCK_ID VALUE
---------- ----------
1 5
2 5
3 5
4 0
5 0
6 4
7 4
9 5
10 5
12 2
SQL> select min(block_id) || '-' || max(block_id) block_range, value
2 from (select block_id, value, max(grp) over (partition by value order by block_id) grp
3 from (select block_id, value,
4 case
5 when lag(block_id) over (partition by value order by block_id) < block_id - 1
6 then
7 row_number() over (partition by value order by block_id)
8 when row_number() over (partition by value order by block_id) = 1 then 1
9 else null
10 end grp
11 from data
12 where value != 0))
13 group by value, grp
14 order by min(block_id);
BLOCK_RANG VALUE
---------- ----------
1-3 5
6-7 4
9-10 5
12-12 2
You do not need PL/SQL to do this, a simple query will do:
CREATE TABLE test(
a INTEGER,
b INTEGER
);
INSERT INTO test VALUES (1, 5);
INSERT INTO test VALUES (2, 5);
INSERT INTO test VALUES (3, 5);
INSERT INTO test VALUES (4, 0);
INSERT INTO test VALUES (5, 0);
INSERT INTO test VALUES (6, 4);
INSERT INTO test VALUES (7, 4);
select min(a) || '-' || max(a) from test group by b order by 1;
An alternative approach is to use aggregate functions:
select distinct
min(block_id) over (partition by value) || '-' ||
max(block_id) over (partition by value)
from whatever_your_table_is_called
where value > 0;