SQL create a conditional sequence

Question

I want to create a conditional int field called Sequence for each group of IDs.

The value 1 is given to Sequence for the first occurrence of a condition, otherwise increment the last count by 1. There are finite list of values for the field condition as illustrated below.

For a new group of ID, Sequence should initialise and start counting from 1.

ID	Date	Condition	Seq
01	01Jun14	AAAAAAAAA	1
01	02Jun14	AAAAAAAAA	2
01	03Jun14	BBBBBBBBB	1
01	04Jun14	BBBBBBBBB	2
01	05Jun14	AAAAAAAAA	3
01	06Jun14	BBBBBBBBB	3
01	07Jun14	EEEEEEEEE	1
02	01Jun14	AAAAAAAAA	1
02	02Jun14	CCCCCCCCC	1
02	03Jun14	CCCCCCCCC	2
02	04Jun14	BBBBBBBBB	1
02	05Jun14	AAAAAAAAA	2
02	06Jun14	BBBBBBBBB	2
03	01Jun14	FFFFFFFFF	1
03	02Jun14	AAAAAAAAA	1
03	03Jun14	AAAAAAAAA	2
03	04Jun14	CCCCCCCCC	1

what have you tried ? This looks like a job for row_number() — Squirrel
– Squirrel, Commented Apr 13, 2022 at 5:43
I have tried ROW_NUMBER() OVER(PARTITION by ID ORDER BY ID, Condition) as Seq. This gives just a row number. — Will Kim
– Will Kim, Commented Apr 13, 2022 at 5:55

DhruvJoshi · Accepted Answer · 2022-04-13 06:09:03Z

2

As pointed out in the comments by Squirrel, this is a job for row number Basically

Seq = ROW_NUMBER() OVER (PARTITION BY ID, CONDITION ORDER BY DATE ASC)

If you need to incorporate this into your code while Insert statement, you'd do something like this

DECLARE @Seq INT
SELECT @Seq= ROW_NUMBER() OVER (PARTITION BY ID, CONDITION  ORDER BY DATE ASC) FROM <yourtable> WHERE ID=@ID AND CONDITION= @condition 

INSERT INTO <yourtable> VALUES
(@ID, @date, @condition, ISNULL(@Seq,1))

answered Apr 13, 2022 at 6:09

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user18098820 · Accepted Answer · 2022-04-13 06:14:40Z

You need to use partition by condition, id in your row_number so that the row_numbers start at 1 for each combination of condition and id.
I have created a table with your required numbering so as to check that the calulated values are what you need.

select
  ID,
  d "Date" ,
  Condition,
  Seq seq_required,
  row_number() over (partition by condition, id order by d) seq_calculated
from t
order by id, d
GO

ID | Date       | Condition | seq_required | seq_calculated
-: | :--------- | :-------- | -----------: | -------------:
 1 | 2014-06-01 | AAAAAAAAA |            1 |              1
 1 | 2014-06-02 | AAAAAAAAA |            2 |              2
 1 | 2014-06-03 | BBBBBBBBB |            1 |              1
 1 | 2014-06-04 | BBBBBBBBB |            2 |              2
 1 | 2014-06-05 | AAAAAAAAA |            3 |              3
 1 | 2014-06-06 | BBBBBBBBB |            3 |              3
 1 | 2014-06-07 | EEEEEEEEE |            1 |              1
 2 | 2014-06-01 | AAAAAAAAA |            1 |              1
 2 | 2014-06-02 | CCCCCCCCC |            1 |              1
 2 | 2014-06-03 | CCCCCCCCC |            2 |              2
 2 | 2014-06-04 | BBBBBBBBB |            1 |              1
 2 | 2014-06-05 | AAAAAAAAA |            2 |              2
 2 | 2014-06-06 | BBBBBBBBB |            2 |              2
 3 | 2014-06-01 | FFFFFFFFF |            1 |              1
 3 | 2014-06-02 | AAAAAAAAA |            1 |              1
 3 | 2014-06-03 | AAAAAAAAA |            2 |              2
 3 | 2014-06-04 | CCCCCCCCC |            1 |              1

db<>fiddle here

Nagib Mahfuz · Accepted Answer · 2022-04-13 06:08:32Z

1

I think dense_rank() should do the trick.

select 
ID,
[Date],
Condition,
dense_RANK() OVER(PARTITION by ID ORDER BY [Date]) as Seq
from Yourtable

answered Apr 13, 2022 at 6:08

Nagib Mahfuz

83310 silver badges21 bronze badges

Comments

Tomak · Accepted Answer · 2022-04-13 10:54:07Z

1

SELECT ID,Condition,COUNT(con) as seq FROM "your table name" GROUP BY con

SELECT ID,Condition,COUNT(con) as seq FROM "your table name" where ID=1 GROUP BY con

desc:

1- for counting all type of Conditional in table

2- for counting each type of Conditional in each group of IDs

edited Apr 13, 2022 at 10:54

answered Apr 13, 2022 at 10:36

Tomak

313 bronze badges

Collectives™ on Stack Overflow

SQL create a conditional sequence

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related