question

pathryck1 avatar image
pathryck1 asked

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

sql-server-2012oracle-sql-developer
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

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)


10 |1200 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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