4
I have a table and I need to return the following: check based on recipient column, add the 3 largest amount of that recipient, bring in query only if the sum of the 3 largest ones are >= 1024:
CREATE TABLE TRANSFERS(
SENDER VARCHAR2(200) NOT NULL,
RECIPIENT VARCHAR2(200) NOT NULL,
DATA DATE NOT NULL,
AMOUNT INTEGER NOT NULL);
INSERT INTO TRANSFERS VALUES ('Smith', 'Williams', '01/01/2000', 200);
INSERT INTO TRANSFERS VALUES ('Smith', 'Taylor', '27/09/2002', 1024);
INSERT INTO TRANSFERS VALUES ('Smith', 'JOHNSON', '26/06/2005', 512);
INSERT INTO TRANSFERS VALUES ('Williams', 'JOHNSON', '17/12/2010', 100);
INSERT INTO TRANSFERS VALUES ('Williams', 'JOHNSON', '22/03/2004', 10);
INSERT INTO TRANSFERS VALUES ('Brown', 'JOHNSON', '20/03/2013', 500);
INSERT INTO TRANSFERS VALUES ('JOHNSON', 'Williams', '02/06/2007', 400);
INSERT INTO TRANSFERS VALUES ('JOHNSON', 'Williams', '26/06/2005', 400);
INSERT INTO TRANSFERS VALUES ('JOHNSON', 'Williams', '26/06/2005', 200);
in this scenario the SELECT should bring the names of Taylor and JHONSON because, Taylor in a transfer he already has the value of 1024, already Jhonson the sum of his 3 largest as Recipient is: 512, 500 e 100 = 1112;
I was only able to bring Taylor:
SELECT T.RECIPIENT AS NOME
FROM TRANSFERS T
WHERE T.AMOUNT >= 1024;
thank you!
This same problem, even stated, follows solution with Mysql (or other Dbs that accept variables): https://answall.com/questions/193752/
– Bacco
Look how it turned out: https://prnt.sc/jzz21c. If you hadn’t erased it I was already answering.
– user28595