# question

## Count pairs in a consecutive order

I have the following example:

COLUMN
19
20
26
28
29
32
33
34

I need to count the rows based on pairs, 19-20, 28-29, 32-33. I'm having difficulty to check if a pair is already counted or not, any sugestions ? The result should be something like this:

CNT
2
1
2
2
1

Also, if possible, can i do it in both languages? pl/sql and sql server

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

I don't know pl/sql, but here's how I would approach this in SQL Server.

I've left the code quite verbose (I'm sure some of the subqueries could be squashed down into one) to help understand the process of each 'pass' over the data

firstpass uses 'gaps and islands' technique to identify the groupings of consecutive values

secondpass then applies a modulo function to identify the start of each pair within the grouping - essentially every odd number is a pair-start

thirdpass then identifies those pairs and gives them a grouping identifier

Finally we can then group on this and produce the output

declare @YourTable table (YourColumn int);

insert into @YourTable select 19;
insert into @YourTable select 20;
insert into @YourTable select 26;
insert into @YourTable select 28;
insert into @YourTable select 29;
insert into @YourTable select 32;
insert into @YourTable select 33;
insert into @YourTable select 34;

select
cast(min(thirdpass.YourColumn) as varchar)
+ '-'
+ cast(max(thirdpass.YourColumn) as varchar) as Pairing,
count(*) as CNT
from
(
select
secondpass.YourColumn, secondpass.grp, secondpass.isstartofpair,
sum(secondpass.isstartofpair)
over (partition by secondpass.grp
order by secondpass.YourColumn rows unbounded preceding) as pair_grp
from
(
select
firstpass.YourColumn,
firstpass.grp,
row_number()over(partition by firstpass.grp
order by firstpass.YourColumn)%2 as isstartofpair
from
(
select
YourColumn,
YourColumn - row_number()over(order by YourColumn) as grp
from @YourTable
) firstpass
) secondpass
) thirdpass
group by thirdpass.grp, thirdpass.pair_grp
order by grp;

Pairing       CNT
------------- -----------
19-20         2
26-26         1
28-29         2
32-33         2
34-34         1

(5 rows affected)

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.