none
Delete the records based on the multiple of counter RRS feed

  • Question

  • Hi Team,

    I need help in deleting the data based on the counter of a group.

    Ex: If the counter of group is 3 then all records within that counter (if there are exact 3 records) or multiple of that counters should be deleted.

    Below is data and expected output:

    --table stores the actual data
    DECLARE @TABLE1 TABLE
    (
    	COL1 VARCHAR(8),
    	COL2 INT,
    	RANK INT
    )
    INSERT INTO @TABLE1
    SELECT 'NF1',1,1
    UNION ALL
    SELECT 'NF2',1,1
    UNION ALL
    SELECT 'NF2',1,2
    UNION ALL
    SELECT 'NF3',1,1
    UNION ALL
    SELECT 'NF3',1,2
    UNION ALL
    SELECT 'NF3',1,3
    UNION ALL
    SELECT 'NF3',1,4
    UNION ALL
    SELECT 'NF4',1,1
    UNION ALL
    SELECT 'NF4',1,2
    UNION ALL
    SELECT 'NF4',1,3
    UNION ALL
    SELECT 'NF4',1,4
    UNION ALL
    SELECT 'NF5',1,1
    UNION ALL
    SELECT 'NF5',1,2
    UNION ALL
    SELECT 'NF5',1,3
    UNION ALL
    SELECT 'NF5',1,4
    UNION ALL
    SELECT 'NF5',1,5
    UNION ALL
    SELECT 'NF1',2,1
    UNION ALL
    SELECT 'NF1',2,1
    UNION ALL
    SELECT 'NF2',2,2
    UNION ALL
    SELECT 'NF2',2,1
    UNION ALL
    SELECT 'NF2',2,2
    
    
    --table stores the counter for a group
    DECLARE @TABLE2 TABLE
    (
    	COL1 VARCHAR(8),
    	COUNT INT
    )
    INSERT INTO @TABLE2
    SELECT 'NF1',2
    UNION ALL
    SELECT 'NF2',2
    UNION ALL
    SELECT 'NF3',3
    UNION ALL
    SELECT 'NF4',2
    UNION ALL
    SELECT 'NF5',2
    
    --Expected output table1 data should be deleted based on table2 counter
    --FOR COL2 = 1
    	--For NF1 count is 2 (in table2) and there is only one record (in table1) so it should not be deleted
    	--For NF2 count is 2 (in table2) and there are two records (in table1) so both should be deleted
    	--For NF3 count is 3 (in table2) and there are four records (in table1) so three should be deleted and fourth be retained
    	--For NF4 count is 2 (in table2) and there are four record (in table1) so all four should be deleted because 2*2 = 4 multiple of counter for that group
    	--For NF5 count is 2 (in table2) and there are five records (in table1) so four should be deleted and fifth be retained
    --FOR COL2 = 2
    	--For NF1 count is 2 (in table2) and there are two records (in table1) so both should be deleted
    	--For NF2 count is 2 (in table2) and there are three records (in table1) so 2 should be deleted and third be retained
    
    SELECT 'NF1',1,1
    UNION ALL
    SELECT 'NF3',1,4
    UNION ALL
    SELECT 'NF5',1,5
    UNION ALL
    SELECT 'NF2',2,5

    Appreciate your inputs

    Thanks,

    Eshwar.


    • Edited by Eswararao C Wednesday, September 11, 2019 9:08 AM
    Wednesday, September 11, 2019 9:00 AM

All replies

  • Check this:

     

    ;

    with T as

    (

           select ROW_NUMBER() over (order by (select NULL)) as id, *

           from @TABLE1

    ),

    N as

    (

           select *, ROW_NUMBER() over (partition by COL2, COL1 order by [RANK] ) as rn

           from T

    ),

    C as

    (

           select COL1, COL2, COUNT(*) as cnt

           from T

           group by COL2, COL1

    )

    delete from t1

    from T as t1

    inner join @TABLE2 as t2 on t2.COL1 = t1.COL1

    inner join N on N.id = t1.id

    inner join C on C.COL1 = t1.COL1 and C.COL2 = t1.COL2

    where N.rn <= C.cnt - C.cnt % t2.[COUNT]

     

    --

     

    select *

    from @TABLE1

    order by COL2, COL1

     

     

    However, it is not clear how to calculate the final ranks.

    Wednesday, September 11, 2019 10:52 AM
  • Hi Eswararao C,

     

    Please try following script. However , in my result there is different something from you.  Please check it.

     
    --table stores the actual data
    DECLARE @TABLE1 TABLE
    (
    	COL1 VARCHAR(8),
    	COL2 INT,
    	RANK INT
    )
    INSERT INTO @TABLE1
    SELECT 'NF1',1,1
    UNION ALL
    SELECT 'NF2',1,1
    UNION ALL
    SELECT 'NF2',1,2
    UNION ALL
    SELECT 'NF3',1,1
    UNION ALL
    SELECT 'NF3',1,2
    UNION ALL
    SELECT 'NF3',1,3
    UNION ALL
    SELECT 'NF3',1,4
    UNION ALL
    SELECT 'NF4',1,1
    UNION ALL
    SELECT 'NF4',1,2
    UNION ALL
    SELECT 'NF4',1,3
    UNION ALL
    SELECT 'NF4',1,4
    UNION ALL
    SELECT 'NF5',1,1
    UNION ALL
    SELECT 'NF5',1,2
    UNION ALL
    SELECT 'NF5',1,3
    UNION ALL
    SELECT 'NF5',1,4
    UNION ALL
    SELECT 'NF5',1,5
    UNION ALL
    SELECT 'NF1',2,1
    UNION ALL
    SELECT 'NF1',2,1
    UNION ALL
    SELECT 'NF2',2,2
    UNION ALL
    SELECT 'NF2',2,1
    UNION ALL
    SELECT 'NF2',2,2
    
    
    --table stores the counter for a group
    DECLARE @TABLE2 TABLE
    (
    	COL1 VARCHAR(8),
    	COUNT INT
    )
    INSERT INTO @TABLE2
    SELECT 'NF1',2
    UNION ALL
    SELECT 'NF2',2
    UNION ALL
    SELECT 'NF3',3
    UNION ALL
    SELECT 'NF4',2
    UNION ALL
    SELECT 'NF5',2
    
    
    ;with cte as (
    select *,count(*)over(partition by COL2,COL1) count_rank 
    from @TABLE1
    ),cte1 as(
    select a.COL1,COL2,RANK,count_rank,COUNT,
    case when count_rank<COUNT then 1 
    when count_rank>=COUNT and count_rank%COUNT=0 then 0
    else 1 end result 
    from cte  a 
    join @TABLE2 b on a.COL1=b.COL1)
    select COL1,COL2,max(RANK) RANK 
    from cte1 where result=1
    group by COL1,COL2
    /*
    COL1     COL2        RANK
    -------- ----------- -----------
    NF1      1           1
    NF2      2           2
    NF3      1           4
    NF5      1           5
    */
    
    

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 12, 2019 8:12 AM
  • how did you get

    SELECT 'NF2',2,5

     in output?

    Looks like a typo to me


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, September 12, 2019 8:34 AM
  • Telco I need help in deleting the data based on the counter of a group.

    Ex: If the counter of group is 3 then all records [sic] within that counter (if there are exact 3 records [sic] [sic]) or multiple of that counters should be deleted.

    Rose are nothing whatsoever like records [sic] . This is a fundamental concept. Your attempt at DDL has a lot of problems. You don't understand that a table must have a key. This is not an option! Since this is just sample data I want to comment on the really bad data element names you picked, but "RANK" is a reserved word in SQL and should never never be used for column name. So first thing let's fix your DDL. Then let's actually use the ANSI/ISO standard insertion statement instead of the old (very old) Sybase kludge:

    CREATE TABLE Foobar
    (col_1 VARCHAR(8) NOT NULL, 
     col_2 INTEGER NOT NULL, 
     col_3 INTEGER NOT NULL, 
     PRIMARY KEY (col_1, col_2, col_3) ); -- not an option!!

    This is the proper syntax using row constructions to do an insertion into a table. Unfortunately you posted data with duplicate rows, so by definition this is not a table. Before I go any further, I would like you to fix your data to make it usable. Here's my guess at what you might have meant, with guesses at data corrections.

    INSERT INTO Foobar
    VALUES 
    ('NF1', 1, 1), 
    ('NF1', 2, 1), ('NF1', 2, 2), 
    ('NF2', 1, 1), ('NF2', 1, 2), 
    ('NF2', 2, 2), ('NF2', 2, 1), ('NF2', 2, 3),
    ('NF3', 1, 1), ('NF3', 1, 2), ('NF3', 1, 3), ('NF3', 1, 4), 
    ('NF4', 1, 1), ('NF4', 1, 2), ('NF4', 1, 3), ('NF4', 1, 4), 
    ('NF5', 1, 1), ('NF5', 1, 2), ('NF5', 1, 3), ('NF5', 1, 4), ('NF5', 1, 5);

    You might want to look up the CREATE SEQUENCE statement to see that applies in your case.

    SQL programmers would probably not materialize the counts the way you're doing, but would use a VIEW. You also don't know that COUNT is a reserved word and should not be used as a column name. 

    CREATE VIEW Foobar_Counts
    AS
    SELECT col_1, col_2, COUNT(*) AS col_3_cnt
       FROM Foobars
     GROUP BY col_1, col_2; 

    But here is your DDL with the proper constraints etc.:

    CREATE TABLE Foobar_Counts 
    (col_1 VARCHAR(8) NOT NULL PRIMARY KEY, --- Not an option 
     foobar_cnt INTEGER DEFAULT 0 NOT NULL); -- valid column name
    INSERT INTO Foobar_Counts
    VALUES ('NF1', 2), ('NF2', 2), ('NF3', 3), ('NF4', 2), ('NF5', 2);

    I've been teaching SQL for over 30 years and I have seen this kind of fundamental conceptual error in new programmers. Since you still think in terms of punchcards and physical storage, you have to materialize the intermediate data. In SQL, we have CTEs, subquery expressions and views. All of these constructs are virtual and are not materialized. 

    Would you like to try again with usable DDL and data?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, September 12, 2019 6:27 PM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 17, 2019 8:33 AM
  • Hi ,

     

    I am writing to follow up this thread with you again. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    5 hours 14 minutes ago