Deleting row from a table having the row number as 1.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 20-Jan-21 16:40pm

aakar

Updated 8-Apr-21 16:16pm

RickZeeland

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Christian Graus · Answer 1 · 2021-01-20T16:43:00

Solution 1

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

Posted 20-Jan-21 16:43pm

Christian Graus

Richard Deeming · Answer 2 · 2021-01-20T22:17:00

Try:

SQL

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)
        )
    )
;

TJohns · Answer 3 · 2021-04-08T16:16:00

Solution 3

DELETE FROM TableName
WHERE SalesID IN (
SELECT SalesID FROM TableName
GROUP BY TerritoryGroup
HAVING MAX(RowNumber) = 1
)

Posted 8-Apr-21 16:16pm

TJohns