Assign group identifer to records

Question

1.00/5 (1 vote)

See more:

Assume there is a table having TempId (which will have value like 1
 or 2), then RID (AutoID), GroupId (Which we have to generate). 
  
What is needed.
 
  	* All continuous record  having TempID = 2 should get GroupID = 1
 	* If there is record with TempID = 1 then the sequence gets break.
 	* No need to assign GroupId to record having TempID = 1
 	* So all 2's will have same groupid, but you have to break the group
 when there is next 1.
 	* NO USE OF WHILE LOOP.

 Expected Output:- 


	Tmld	Rld	GroupId
1     1     1   NULL
2	  1     2   NULL	
3	  1	    3	NULL	
4     1	    4	NULL	
5     2	    5	1
6	  2	    6	1	
7	  1	    7	NULL	
8		1	8	NULL	
9		2	9	2
10		2	10	2
11		2	11	2
12		2	12	2
13		2	13	2
14		1	14	NULL	
15		2	15	3
16		2	16	3
17		2	17	3
18		2	18	3
19		2	19	3
20		2	20	3
21		2	21	3
Z2		2	Z2	3

What I have tried:

SELECT  rid, TempId,
       CASE 
          WHEN TempId = 1 THEN 0	
          ELSE DENSE_Rank() OVER (PARTITION BY TempId, grp 
                                  ORDER BY rid) 
       END AS Sequence
	FROM (      
	   SELECT  rid, TempId,
			  DENSE_Rank() OVER (ORDER BY rid) - 
			  DENSE_Rank() OVER (PARTITION BY tempid 
								 ORDER BY rid) AS grp
	   FROM zz_aud_ankit ) AS t 
	   --) as p
	ORDER BY rid

Posted 20-Oct-21 21:47pm

Member 14156756

Updated 22-Oct-21 1:21am

Add a Solution

Comments

OriginalGriff 21-Oct-21 4:13am

And?
What does it do that you didn't expect, or not do that you did?
What have you tried to do to find out why?
Are there any error messages, and if so, where and when? What did you do to make them happen?

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
Use the "Improve question" widget to edit your question and provide better information.

2 solutions

Solution 1

In your version you have explicitly set the group for ALL TempId = 1 to 0, not just the first group. In a similar way you are ranking ALL TempId = 2 which is why your query gives the following results

rid	TempId	Sequence
1	1	0
2	1	0
3	1	0
4	1	0
5	2	1
6	2	2
7	1	0
8	1	0
9	2	1
10	2	2
11	2	3
12	2	4
13	2	5
14	1	0
15	2	1
16	2	2
17	2	3
18	2	4
19	2	5
20	2	6
21	2	7
22	2	8

You are not so much interested in the rank of row as you need the row that came before - i.e. you can use the LAG function. E.g.

SQL

SELECT Tempid, rid
    ,LAG(tempid,1, tempid) OVER (ORDER BY rid) prev
FROM zz_aud_ankit

Which gives these results

Tempid	rid	prev
1	1	1
1	2	1
1	3	1
1	4	1
2	5	1
2	6	2
1	7	2
1	8	1
2	9	1
2	10	2
2	11	2
2	12	2
2	13	2
1	14	2
2	15	1
2	16	2
2	17	2
2	18	2
2	19	2
2	20	2
2	21	2
2	22	2

Note the row where Rid = 5 and 6 though, more work is required to get the proper grouping. By using a simple "toggle" to check if TempId = Prev and summing that toggle we can get the proper grouping ...

SQL

;WITH cte1 AS 
(
	SELECT Tempid, rid
		,LAG(tempid,1, tempid) OVER (ORDER BY rid) prev
	FROM zz_aud_ankit
)
SELECT Tempid, Rid, prev
	,sum(CASE WHEN tempid = prev THEN 0 ELSE 1 END) 
		OVER (ORDER BY rid ) AS groupid
FROM cte1

Which gives results

Tempid	Rid	prev	groupid
1	1	1	0
1	2	1	0
1	3	1	0
1	4	1	0
2	5	1	1
2	6	2	1
1	7	2	2
1	8	1	2
2	9	1	3
2	10	2	3
2	11	2	3
2	12	2	3
2	13	2	3
1	14	2	4
2	15	1	5
2	16	2	5
2	17	2	5
2	18	2	5
2	19	2	5
2	20	2	5
2	21	2	5
2	22	2	5

That is also not quite your expected results but should be enough to point you in the right direction

Posted 21-Oct-21 1:54am

CHill60

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 14156756 · Accepted Answer · 2021-10-22T01:21:00

Answer :- I Tried this solution from my side..

SELECT  rid, TempId,
       CASE 
          WHEN TempId = 1 THEN NULL         
          ELSE DENSE_Rank() OVER (PARTITION BY TempId 
                                  ORDER BY grp) 
       END AS GroupId
FROM (      
                 SELECT  rid, TempId,rid - ROW_NUMBER() OVER (PARTITION BY tempid ORDER BY rid) AS grp
FROM zz_aud_ankit ) AS t 
ORDER BY rid