remove duplicates

Question

hi all,

I have this kind of duplicates:

ID Line Amount
=== === ======
100 1 1000
100 2 100
100 1 100
100 2 1000
101 1 2000
101 2 500
101 3 50
101 1 50
101 2 2000
101 3 500

I would like to remove the lines where the amount corresponding to Line 1 is smaller than the amount for Line 2, in any ID group that has either 2 or 3 records. Namely, from the set above I would like to remove:

ID Line Amount
=== === ======
100 1 100
100 2 1000
101 1 50
101 2 2000
101 3 500

so that finally I remain with:

ID Line Amount
=== === ======
100 1 1000
100 2 100
101 1 2000
101 2 500
101 3 50

Thanks,
elsvieta

Answer 1

Hi @elsvieta

Try this query:

CREATE TABLE #Demo (ID INT,Line INT,Amount INT)
INSERT INTO #Demo VALUES
(100 ,1, 1000),
(100, 2, 100),
(100 ,1, 100),
(100 ,2, 1000),
(101 ,1 ,2000),
(101 ,2 ,500),
(101 ,3, 50),
(101 ,1, 50),
(101 ,2, 2000),
(101 ,3, 500)

;WITH CTE AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY ID,Line ORDER BY Amount DESC) AS DESC_Order
        ,ROW_NUMBER()OVER(PARTITION BY ID,Line ORDER BY Amount ASC) AS ASC_Order
FROM #Demo
)
DELETE
FROM CTE
WHERE (Line = 1 AND DESC_Order <> 1) OR (Line <> 1 AND ASC_Order <> 1)

SELECT * FROM #Demo

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

It's not clear to me if you want to delete the data from a table, or if you only want a query to give you the max values.

Here is a query to delete the rows:

; WITH numbering AS (
   SELECT rowno = row_number() OVER(PARTITION BY ID, Line ORDER BY Amount DESC)
   FROM  tbl
)
DELETE numbering WHERE rowno > 1

Here is a query to give you the max values:

SELECT ID, Line, MAX(Amount) AS Amount
FROM   tbl
GROUP BY id, Line