Click here to Skip to main content
14,766,117 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi All,

I have the data in the below format.
I need to delete the row having the RowNumber column as 1. i.e. the row with the SalesID as 286 (TerritoryGroup as Pacific)

Not all the rows with the RowNumber as 1 need to be deleted, just the row with a single RowNumber (i.e not having any further row numbers).

Any help would be much appreciated..
SalesID	TerritoryGroup			SalesLastYear	RowNumber
274			NULL			           0			1
285			NULL			           0			2
287			NULL			           0			3
288			Europe			           1307949.792	1
289			Europe			           1635823.397	2
290			Europe			           2396539.76	3
284			North America	           0			1
283			North America	           1371635.316	2
276			North America	           1439156.029	3
278			North America	           1620276.897	4
275			North America	           1750406.479	5
279			North America	           1849640.942	6
280			North America	           1927059.178	7
277			North America	           1997186.204	8
282			North America	           2038234.655	9
281			North America	           2073506		10
286			Pacific			           2278548.978	1


What I have tried:

Not all the rows with the RowNumber as 1 need to be deleted, just the row with a single RowNumber (i.e not having any further row numbers).
Posted
Updated 20-Jan-21 23:17pm
v2

Use a CTE and a group by to filter your records where there's only one row for a territory. Then delete based on that
   
Try:
DELETE
FROM
    T1
FROM
    YourTable As T1
WHERE
    T1.RowNumber = 1
And
    Not Exists
    (
        SELECT 1
        FROM YourTable As T2
        WHERE T2.RowNumber != 1
        And 
        (
            T2.TerritoryGroup = T1.TerritoryGroup 
        Or 
            (T2.TerritoryGroup Is Null And T1.TerritoryGroup Is Null)
        )
    )
;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900