How do I row count into groups?

Question

I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them in set 1. Next three are 0,0,1 and I want them in set 2.

Order is important as I'm matching against a pattern table to get an overall count of the binary patterns.

I can see that Row would help but I don't know how to make row count by 3'ss.

Thank you.

Answer 1

Rows in a table don't have order. You will need an additional column to enforce the order, if order is important for you.

Answer 2

If you have a column that preserves order, here is how you could do it in Oracle. You will have to translate to SQL Server.

SELECT f1a, f1b, f1c, count(*) FROM (
   SELECT LAG(f1,2) OVER (ORDER BY f2) f1a, LAG(f1, 1) OVER (ORDER BY f2) f1b, f1 f1c, f2 
   from t1
   )
WHERE MOD(f2,3) = 0
GROUP BY f1a, f1b, f1c;

Test Data:

--DROP TABLE t1;
CREATE TABLE t1 AS 
   SELECT 0 f1, 1 f2 from dual
   UNION ALL
   SELECT 1, 2 from dual
   UNION ALL
   SELECT 0, 3 from dual
   UNION ALL
   SELECT 1, 4 from dual
   UNION ALL
   SELECT 0, 5 from dual
   UNION ALL
   SELECT 0, 6 from dual
   UNION ALL
   SELECT 0, 7 from dual
   UNION ALL
   SELECT 0, 8 from dual
   UNION ALL
   SELECT 1, 9 from dual
   UNION ALL
   SELECT 1, 10 from dual
   UNION ALL
   SELECT 0, 11 from dual
   UNION ALL
   SELECT 0, 12 from dual
;

Answer 3

Based on the following assumptions:

1. You already know what order you want your data in and how to do it - in particular, your ordering does not allow ties
2. You have 1 bit field in each row
3. You want to concatenate the bit fields in each 'set' of 3 'adjacent' rows
4. Your version of SQL Server supports windowing functions

You can use the windowing function row_number() and divide by three to group the rows into 3s, perhaps like this:

testbed:

create table t(id int identity, flag bit default round(rand(),0))
while (select count(*) from t)<21
begin
  insert into t default values
end

query:

select grp, str(sum(case rnk%3 when 0 then cast(flag as integer) end))+
            str(sum(case rnk%3 when 1 then cast(flag as integer) end))+
            str(sum(case rnk%3 when 2 then cast(flag as integer) end)) pattern
from( select id, flag, row_number() over (order by id) - 1 as rnk, 
             floor((row_number() over (order by id) - 1)/3) as grp
      from t ) as z
group by grp

result:

grp          pattern
0            0         1         0
1            0         0         0
2            1         1         1
3            0         1         0
4            0         0         0
5            1         0         1
6            1         0         0

You'll need to substitute your ordering for my order by ids - and probably do something with the pattern to make it match with your pattern table :)