SQL Server – Update Group Number Incrementally by 1 Based on Group

group bysql serversql-server-2012stored-procedurest-sql

I work on SQL server 2012 I face issue i can't update group no to every steps from 1 to 3
meaning i need every group from 1 to 3 take identity number incremental by 1 as 1,2,3 etc ..

create table #Replacement
       (
       PartIDC  INT,
       PartIDX  INT,
       FlagStatus nvarchar(50),
       HasReplacement nvarchar(50),
       groupId int,
       step tinyint
       )
    insert into #Replacement (PartIDC,PartIDX,FlagStatus,HasReplacement,groupId,step)
    values 
    
 (1222, 3421, 'Invalid', 'Critical', NULL, 1),
 (3421, 6421, 'Valid' ,    'Active' ,   NULL, 2),
 (1222, 6421, 'Valid' ,    'Chain',     NULL, 3),
 (5643, 2243, 'Invalid', 'Critical', NULL, 1),
 (2243, 3491, 'Valid' ,    'Active',   NULL, 2),
 (5643, 3491, 'Valid' ,    'Chain',     NULL, 3)

select * from #Replacement

Expected result

   PartIDC PartIDX FlagStatus HasReplacement groupId step
    1222 3421 Invalid           Critical      1      1 
    3421 6421 Valid              Active       1      2
    1222 6421 Valid           Chain           1      3
    5643 2243 Invalid           Critical      2      1
    2243 3491 Valid           Active          2      2
    5643 3491 Valid           Chain           2      3

always step 1 and step 3 are both equal on PartID C

always step 1 and step 2 are both equal on PartID x from step 1 equal to PartID C from step 2 .

so How to do Expected result above by update statement to group Id ?

Best Answer

The statement below will first create your groups based upon the PartIDC of those records with a Step = 1, and then use that for the subsequent steps. We then model the results and join to that to update the table:

 ;WITH 
 Step1 AS 
 (
     SELECT 
        PartIDC,
        PartIDX,
        FlagStatus,
        HasReplacement,
        groupId = ROW_NUMBER() OVER (ORDER BY PartIDC),
        step 
     FROM #Replacement WHERE step = 1
),
Step3 AS 
 (
    SELECT 
        r.PartIDC,
        r.PartIDX,
        r.FlagStatus,
        r.HasReplacement,
        s1.groupId,
        r.step 
    FROM #Replacement r JOIN Step1 s1 ON r.PartIDC = s1.PartIDc and r.Step =3
),
Step2 AS 
 (
    SELECT 
        r.PartIDC,
        r.PartIDX,
        r.FlagStatus,
        r.HasReplacement,
        s1.groupId,
        r.step 
    FROM #Replacement r 
        JOIN Step1 s1 ON r.PartIDC = s1.PartIDX and r.Step =2
),
NewValues AS 
(
    SELECT * FROM Step1  
    UNION ALL 
    SELECT * FROM STEP2
    UNION ALL 
    SELECT * FROM STEP3
)
UPDATE r
SET GroupID = nv.GroupID
FROM
    #Replacement r
        JOIN NewValues NV ON r.PartIDC = nv.PartIDC AND r.PartIDX = nv.PartIDX

This query:

SELECT * FROM #Replacement
ORDER BY GroupID, Step

Now produces:

PartIDC     PartIDX     FlagStatus                                         HasReplacement                                     groupId     step
----------- ----------- -------------------------------------------------- -------------------------------------------------- ----------- ----
1222        3421        Invalid                                            Critical                                           1           1
3421        6421        Valid                                              Active                                             1           2
1222        6421        Valid                                              Chain                                              1           3
5643        2243        Invalid                                            Critical                                           2           1
2243        3491        Valid                                              Active                                             2           2
5643        3491        Valid                                              Chain                                              2           3

Related Solutions

Sql-server – Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

Depending on your permissions, the linked server could be trying to stream all the data over locally and then doing filtering. References

You might be able to skip that pain by computing the total aggregate count first into a table on the local server and then beat against that.

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT 
    p.package_uuid
,   count(d.external_identification) AS [count]
FROM 
    ServerB.DATABASE.dbo.package p
    INNER JOIN 
        ServerB.DATABASE.dbo.doc2 d
        ON p.package_id = d.package_id
GROUP BY 
    p.package_uuid;

Try running that query locally on ServerB first to get an understanding of the theoretical throughput without factoring in your network. You can then do some quick and dirty estimates based on data sizes (500 + 8 per row in temporary table) and then it depends on your network. Hopefully this is all local network.

If the time is significantly different between the run on ServerB and pulling it back over, then you might need to use the OPENQUERY syntax to force the join on the remote server. Code approximately

CREATE TABLE #LOCAL
(
    package_uuid nvarchar(255) NOT NULL PRIMARY KEY CLUSTERED
,   [count] bigint
);

INSERT INTO
    #LOCAL
SELECT
    OQ.package_uuid
,   OQ.[count]
FROM
    OPENQUERY(ServerB,
    N'
    SELECT 
        p.package_uuid
    ,   count(d.external_identification) AS [count]
    FROM 
        DATABASE.dbo.package p
        INNER JOIN 
            DATABASE.dbo.doc2 d
            ON p.package_id = d.package_id
    GROUP BY 
        p.package_uuid
    ) AS OQ;

SQL Server – Update All Rows with Random Foreign Key from Another Table

Not going to go through what you're trying to accomplish, but for your update, you're using a TOP without an ORDER BY. While SQL Server doesn't guarantee ordering for such queries, it doesn't just randomly choose, either. If you want to force a random row to be returned, you'll need to order by a row-level random number. For that, you can use ORDER BY ABS( CHECKSUM( NEWID() ) ).

Try it out against sys.objects to see the behavior of explicitly ordering randomly.

SELECT  TOP 1 name
FROM    sys.objects
ORDER BY ABS( CHECKSUM( NEWID() ) );

And good luck with whatever it is you're doing.

Edit:

@jean points out in the comments just NEWID() is all that is necessary, which is 100% correct, I just muscle-memory the ABS( CHECKSUM() ) since I tend to need row-level randomization with a % operator.

Edit 2:

So something like this, then?

UPDATE  t
    SET CategoryID = nt.CategoryID
FROM    dbo.Topic t
INNER JOIN (    SELECT  t.ID, ucl.CategoryID, 
                        Ordinal = ROW_NUMBER() OVER (
                            PARTITION BY t.ID
                            ORDER BY NEWID() )
                FROM    dbo.Topic t
                INNER JOIN dbo.UserCategoryLink ucl
                    ON  t.UserID = ucl.UserID ) nt
    ON  t.ID = nt.ID
WHERE   nt.Ordinal = 1;

Best Answer

Related Solutions

Sql-server – Updating a local table with a per-row count(*) which is an aggregate of inner joins on remote server

SQL Server – Update All Rows with Random Foreign Key from Another Table

Related Question