CODEWITHSUNDEEP
sql-server
user1
user1

Reputation: 4131

Calculate minimum total value and delete all other rows from Table

Here's my table.

Table MyTable
-------------
ID  Distance1   Cost1               Distance2   Cost2       Distance3   Cost3
1   711.9       6196.90432379846    NULL        NULL        NULL        NULL
2   672.4       7316.33             NULL        NULL        103.5       900.941 8217.271
3   787.7       8570.9              252         2193.59     NULL        NULL

What I want is, find out row which has minimum total (Cost1+Cost2+Cost3). Keep that row and delete everything else.

So far I have achieved this. This gives me row which has minimum total value.

select TOP 1 *, ISNULL(Cost1, 0 )+ISNULL(Cost2, 0 )+ISNULL(Cost3, 0 ) as TotalCost from MyTable order by TotalCost

I also want to delete other rows. Is there anyway I can do this in one statement.

Upvotes: 1

Views: 30

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93744

Use CTE and Row_Number window function to delete 

;with cte as
(
select Row_number()over(order by ISNULL(Cost1, 0)+ISNULL(Cost2, 0 )+ISNULL(Cost3, 0)) rn,* 
from MyTable
)
Delete from cte where rn > 1

Upvotes: 1

Related Questions