set identity value per each row group

Question

Hello,

i need to set an identity value grouping the values in columns "ID" and "Result", as you can see in the screenshot the identity value breaks after each 0 and resets after an ID change.

if you have any idea of how do this i'll really appreciate it

Answer 1

Hi @Jefferson Moseton ,

Welcome to the microsoft TSQL forum!

Your question is related to tsql, so my colleague helped you add the tsql tag.

As far as this forum is concerned, we recommend that you publish the table structure and sample data (CREATE and INSERT statements) related to the question at the same time when you post the question. This helps us to copy and test quickly.

The question you are asking cannot actually be realized. Because in SQL Server, the table is an unordered collection, which means that the data in the table is not ordered. The order you see is randomly returned by SQL Server. When you return the data in the table multiple times, you may get a different order. So it is not possible to group and add flags in the order in your screenshot. Because these data are actually in no order. If there are columns that uniquely identify these groupings in your table structure, then your problem is easy to implement.

In order to arrange your data in the order shown in your screenshot, I added an extra column. And the following code only applies to the data in your screenshot:

CREATE TABLE #test(ID int,Result int,log int identity(1,1))  
INSERT INTO #test VALUES(123,0),(123,1),(123,1),(123,1),(123,1)  
                        ,(123,0),(123,1),(123,1),(123,1),(123,1)  
 ,(123,0),(123,1),(123,1),(123,1),(123,1)  
 ,(789,0),(789,0),(789,0),(789,1),(789,1)  
 ,(789,0),(789,1),(789,1)  
  
SELECT ID,Result,  
CASE WHEN Result=0 THEN 0   
ELSE log/6+1 END Output  
FROM #test  
WHERE ID=123  
UNION ALL  
SELECT ID,Result,  
CASE WHEN Result=0 THEN 0   
ELSE log/3-5 END Output  
FROM #test  
WHERE ID=789  

Output:

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.