# Delete the records based on the multiple of counter

• ### 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 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.

Monday, September 23, 2019 9:48 AM