IR Punch IR Punch - 6 months ago 14
SQL Question

Delete some rows to bring each group total below the threshold

I have a

Ledger
table:

CREATE TABLE Ledger
(
PersonID int,
Narration varchar(255),
Payment int(255)
);

INSERT INTO Ledger(PersonID, Narration, Payment)
VALUES (1, 'Snacks 1', 5);
INSERT INTO Ledger(PersonID, Narration, Payment)
VALUES (1, 'Snacks 2', 10);
INSERT INTO Ledger(PersonID, Narration, Payment)
VALUES (2, 'Snacks 3', 7);
INSERT INTO Ledger(PersonID, Narration, Payment)
VALUES (1, 'Snacks 4', 6);
INSERT INTO Ledger(PersonID, Narration, Payment)
VALUES (2, 'Snacks 5', 3);
INSERT INTO Ledger(PersonID, Narration, Payment)
VALUES (1, 'Snacks 6', 1);


The table looks like this:

PersonID Narration Payment
_____________________________________________
1 Snacks 1 5
1 Snacks 2 10
2 Snacks 3 7
1 Snacks 4 6
2 Snacks 5 3
1 Snacks 6 1


Here
PersonID=1
totally spent 22 and
PersonID=2
totally spent 10.

My requirement is to reduce the total
Payment
to be below or equal to 20. There is no unique columns. I wish to delete records to make the total
Payment
below or equal to 20
.

In the above table,
PersonID=1
has a total
Payment
greater than 20, so I need to delete some records to reduce the total payment.

My Expected Output

PersonID Narration Payment
_____________________________________________

1 Snacks 2 10
2 Snacks 3 7
1 Snacks 4 6
2 Snacks 5 3
1 Snacks 6 1


Here I removed

1 Snacks 1 5


Now the total
Payment
of
PersonID=1
is 17, which is below 20.

Based on logic we have to delete the records.

Kindly assist me in both SQL Server and MySQL. My First Preference is SQL Server.

Answer

Here is one possible variant for SQL Server 2012+.

Sample data

CREATE TABLE Ledger
(
    PersonID int,
    Narration varchar(255),
    Payment int
);

INSERT INTO Ledger(PersonID, Narration, Payment) VALUES 
(1, 'Snacks 1', 5),
(1, 'Snacks 2', 10),
(2, 'Snacks 3', 7),
(1, 'Snacks 4', 6),
(2, 'Snacks 5', 3),
(1, 'Snacks 6', 1);

SELECT *
FROM Ledger
ORDER BY PersonID, Payment;

+----------+-----------+---------+
| PersonID | Narration | Payment |
+----------+-----------+---------+
|        1 | Snacks 6  |       1 |
|        1 | Snacks 1  |       5 |
|        1 | Snacks 4  |       6 |
|        1 | Snacks 2  |      10 |
|        2 | Snacks 5  |       3 |
|        2 | Snacks 3  |       7 |
+----------+-----------+---------+

Query

We can calculate the running total to determine which rows we want to keep and which to delete. You can tune the logic of row selection by choosing the sorting in the running total. In this example I'll calculate the running total starting from the smallest Payment, so rows with smallest Payment will remain.

This query shows the calculations, to understand how it works:

WITH
CTE
AS
(
    SELECT
        PersonID
        ,Narration
        ,Payment
        ,SUM(Payment) OVER 
            (PARTITION BY PersonID ORDER BY Payment
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss
    FROM Ledger
)
SELECT *
FROM CTE
ORDER BY PersonID, Payment;


+----------+-----------+---------+----+
| PersonID | Narration | Payment | ss |
+----------+-----------+---------+----+
|        1 | Snacks 6  |       1 |  1 |
|        1 | Snacks 1  |       5 |  6 |
|        1 | Snacks 4  |       6 | 12 |
|        1 | Snacks 2  |      10 | 22 |
|        2 | Snacks 5  |       3 |  3 |
|        2 | Snacks 3  |       7 | 10 |
+----------+-----------+---------+----+

This query actually deletes rows:

WITH
CTE
AS
(
    SELECT
        PersonID
        ,Narration
        ,Payment
        ,SUM(Payment) OVER 
            (PARTITION BY PersonID ORDER BY Payment
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ss
    FROM Ledger
)
DELETE FROM CTE
WHERE ss > 20;

Result

SELECT *
FROM Ledger
ORDER BY PersonID, Payment;

+----------+-----------+---------+
| PersonID | Narration | Payment |
+----------+-----------+---------+
|        1 | Snacks 6  |       1 |
|        1 | Snacks 1  |       5 |
|        1 | Snacks 4  |       6 |
|        2 | Snacks 5  |       3 |
|        2 | Snacks 3  |       7 |
+----------+-----------+---------+
Comments