hello,
i want to bring maximum APPLIEDFEE for each CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE and their relevant TRANSACTIONID. i showed desired rows in the picture. can anyone help me?
you can find create table syntax and database version below the picture.
thanks.
/* Live SQL 22.4.1, running Oracle Database 19c EE Extreme Perf - 19.17.0.0.0 */
CREATE TABLE tableName
(
CATEGORTYPE VARCHAR(512),
CHANNELTYPE VARCHAR(512),
CUSTOMERTYPE VARCHAR(512),
CHARGECODE VARCHAR(512),
APPLIEDFEE INT,
TRANSACTIONID INT
);
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL1', 'CUSTOMERTYPE1', 'CODE1', '10', '10001');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL1', 'CUSTOMERTYPE1', 'CODE1', '10', '10002');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL1', 'CUSTOMERTYPE1', 'CODE1', '5', '10003');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL1', 'CUSTOMERTYPE1', 'CODE1', '7', '10004');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL1', 'CUSTOMERTYPE2', 'CODE1', '20', '10005');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL1', 'CUSTOMERTYPE2', 'CODE1', '14', '10006');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL1', 'CUSTOMERTYPE2', 'CODE1', '20', '10007');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL1', 'CUSTOMERTYPE2', 'CODE1', '6', '10008');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL2', 'CUSTOMERTYPE1', 'CODE1', '14', '10009');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL2', 'CUSTOMERTYPE1', 'CODE1', '7', '10010');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL2', 'CUSTOMERTYPE1', 'CODE1', '3', '10011');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL2', 'CUSTOMERTYPE1', 'CODE1', '14', '10012');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL2', 'CUSTOMERTYPE2', 'CODE1', '87', '10013');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL2', 'CUSTOMERTYPE2', 'CODE1', '58', '10014');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL2', 'CUSTOMERTYPE2', 'CODE1', '87', '10015');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY1', 'CHANNEL2', 'CUSTOMERTYPE2', 'CODE1', '53', '10016');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL1', 'CUSTOMERTYPE1', 'CODE1', '20', '10017');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL1', 'CUSTOMERTYPE1', 'CODE1', '20', '10018');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL1', 'CUSTOMERTYPE1', 'CODE1', '10', '10019');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL1', 'CUSTOMERTYPE1', 'CODE1', '14', '10020');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL1', 'CUSTOMERTYPE2', 'CODE1', '40', '10021');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL1', 'CUSTOMERTYPE2', 'CODE1', '28', '10022');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL1', 'CUSTOMERTYPE2', 'CODE1', '40', '10023');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL1', 'CUSTOMERTYPE2', 'CODE1', '12', '10024');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL2', 'CUSTOMERTYPE1', 'CODE1', '28', '10025');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL2', 'CUSTOMERTYPE1', 'CODE1', '14', '10026');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL2', 'CUSTOMERTYPE1', 'CODE1', '6', '10027');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL2', 'CUSTOMERTYPE1', 'CODE1', '28', '10028');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL2', 'CUSTOMERTYPE2', 'CODE1', '174', '10029');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL2', 'CUSTOMERTYPE2', 'CODE1', '116', '10030');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL2', 'CUSTOMERTYPE2', 'CODE1', '174', '10031');
INSERT INTO tableName (CATEGORTYPE, CHANNELTYPE, CUSTOMERTYPE, CHARGECODE, APPLIEDFEE, TRANSACTIONID) VALUES ('CATEGORY2', 'CHANNEL2', 'CUSTOMERTYPE2', 'CODE1', '106', '10032');