5

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.

3
  • look at this question on StackOverflow: stackoverflow.com/q/7144100/136639, I hope it helps
    – Alex_L
    Aug 23, 2011 at 1:45
  • 2
    How are you identifying "first three rows"? what version of SQL Server? Aug 23, 2011 at 2:32
  • If you have 3 bit columns, there are 8 permutations unless you are saying that only one column or the 3 can be set. Or do you have one bit column where you want to group adjacent rows: we need to know what makes "adjacent". And are there any other bit patterns eg 110?
    – gbn
    Aug 23, 2011 at 4:38

3 Answers 3

3

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

1

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
;
0
0

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 :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.