CODEWITHSUNDEEP
sqlsql-server-2008-r2
Theertha Dinesh
Theertha Dinesh

Reputation: 35

Deleting a row based on the max value in SQL Server 2008 R2

enter image description here

I have @RankTable table which contains debit of different customers. From this I want to delete for each customer who has the highest rank.

Declare @RankTable Table
(
   Customer_ID int,
   Debit decimal(18,2),
   Transaction_Date datetime,
   Sales_Master_ID int,
   Rank int,
   Rank1 int
)
Insert into @RankTable
  Select *,row_number()over(partition by (Customer_ID)  Order By 
    Customer_ID)as Rank1 
From @tempTable


Select  Customer_ID,Debit,Transaction_Date ,Sales_Master_ID,Rank1 from 
@RankTable Order By Customer_ID

I expect the output of the above table is: I want to delete customer who is having maximum rank. For ex:I want to delete the row of Customer_ID = 4 which is having Rank1 = 20 (like this for all customers), but I can't delete

Upvotes: 1

Views: 300

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

This will work-

DELETE A
FROM @RankTable A
INNER JOIN
(
    SELECT ID,MAX(Rank1) Rank1
    FROM @RankTable 
    GROUP BY ID
) B
ON A.id = B.id and A.Rank1 = B.Rank1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Use a CTE, which targets the highest ranking record for each customer.

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Rank1 DESC) rn
    FROM @RankTable
)

DELETE
FROM cte
WHERE rn = 1;

Upvotes: 2

Related Questions