IR Punch - 9 months ago 24

SQL Question

I have a

`Ledger`

`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`

`PersonID=2`

My requirement is to reduce the total

`Payment`

`Payment`

In the above table,

`PersonID=1`

`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`

`PersonID=1`

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 |
+----------+-----------+---------+
```