Assuming that I understand the problem:
- you have a number of rows with sections (“Cates”) and symbols (“Type”);
- if there are any symbols ending in a minus sign then these indicate a row without a minus sign should be removed;
- symbols are never “mixed” per section, i.e. a section can never have “A” and “B-“;
- there will always be a row to remove if there is a type with a minus;
- rows should be removed starting with the lowest Id.
Then this should work:
DECLARE @data TABLE ( Id INT, Cate VARCHAR(5), [Type] VARCHAR(2)); INSERT INTO @data SELECT 1, 'AM001', 'A'; INSERT INTO @data SELECT 2, 'AM001', 'A-'; INSERT INTO @data SELECT 3, 'AM001', 'A'; INSERT INTO @data SELECT 4, 'AM003', 'B'; INSERT INTO @data SELECT 6, 'AM003', 'B'; INSERT INTO @data SELECT 7, 'AM005', 'B'; INSERT INTO @data SELECT 8, 'AM005', 'B-'; INSERT INTO @data SELECT 9, 'AM005', 'B'; INSERT INTO @data SELECT 10, 'AM006', 'A'; INSERT INTO @data SELECT 11, 'AM006', 'A'; INSERT INTO @data SELECT 12, 'AM011', 'B'; INSERT INTO @data SELECT 13, 'AM011', 'B-'; INSERT INTO @data SELECT 14, 'AM011', 'B'; WITH NumberToRemove AS ( SELECT Cate, COUNT(*) AS TakeOff FROM @data WHERE [Type] LIKE '_-' GROUP BY Cate), Ordered AS ( SELECT Id, Cate, [Type], ROW_NUMBER() OVER (PARTITION BY Cate ORDER BY Id) AS RowId FROM @data WHERE [Type] NOT LIKE '_-') SELECT d.* FROM @data d LEFT JOIN NumberToRemove m ON m.Cate = d.Cate INNER JOIN Ordered o ON o.Id = d.Id WHERE o.RowId > ISNULL(m.TakeOff, 0);
The query works by first counting the number of rows to remove from each section (“Cate”) by tallying up the number of symbols with a minus sign per section. Next it sorts the rows where the symbols don’t have a minus sign and assigns each row a number in Id order (“row number”), starting back at 1 for each new section (“Cate”).
Finally I just pick the rows without a minus sign symbol, where the row number is greater than the number that were to be removed. Note that if a section has no rows to remove then it will return NULL rows to remove, so I transform this to 0, because ALL rows in that section with have a row number greater than 0.
My results were:
Id Cate Type 3 AM001 A 4 AM003 B 6 AM003 B 9 AM005 B 10 AM006 A 11 AM006 A 14 AM011 B
If my assumptions were incorrect then this script could easily be amended to suit…