CODEWITHSUNDEEP
sqlsql-servert-sql
Alex S.
Alex S.

Reputation: 155

Group values by first value of group

i want to group values by the first value of group, but seems that i'm blind. Example table

COL_1     COL_2   COL_3        ROW_ID
A         AA      2017-01-01   1
A         BB      2017-01-05   2
A         BB      2017-01-21   3
A         AA      2017-01-22   4
A         BB      2017-01-23   5
A         BB      2017-01-24   6
B         AA      2017-01-01   7
B         BB      2017-01-05   8
B         BB      2017-01-22   9
B         AA      2017-01-24   10
C         BB      2017-01-01   11

The result should look like this

COL_1     COL_2   COL_3        ROW_ID  Group
A         AA      2017-01-01   1       1
A         BB      2017-01-05   2       1
A         BB      2017-01-21   3       1        
A         AA      2017-01-22   4       2
A         BB      2017-01-23   5       2
A         BB      2017-01-24   6       2
B         AA      2017-01-01   7       3
B         BB      2017-01-05   8       3
B         BB      2017-01-22   9       3
B         AA      2017-01-24   10      4
C         AA      2017-01-01   11      5

The number of the group could also be the number of the number of the first ID of each group or any other name, but must be unique. I don't want to use a cursor, just simple T-SQL from SQL-SERVER 2012 and above

I think the answer have something to do with LAG and PARTITION OVER, but didn't find a solution…. so far

Any ideas? Cheers, Alex

EDIT Solution from @Gordon with a small change

select t.*,
  col_1 + '_' + CONVERT(VARCHAR,sum(case when col_2 = 'AA' then 1 else 0 end) over (order by rowid)) as grp
from t;

Thank you again Gordon

Upvotes: 2

Views: 93

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

If I understand correctly, you want to count the number of 'AA' up to each value. If so:

select t.*,
      sum(case when col_2 = 'AA' then 1 else 0 end) over (partition by col_1 order by col3) as grp
from t;

I am guessing that you want separate groups for each col_1. If that is not correct, then:

select t.*,
      sum(case when col_2 = 'AA' then 1 else 0 end) over (order by rowid) as grp
from t;

If you simply want groups of 3 and can depend on row_id:

select t.*, (1 + (rowid - 1)/3) as grp
from t;

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use division with group size on row_number.

select t.*,1+(row_number() over(order by row_id)-1)/3 as grp
from tbl t

Upvotes: 1

Related Questions