Get any maximum value from column containing semicolon separated array in sql
2 answers
The idea is here:
for each balancetype value, extract all the values from the periodbal column, convert them to separate rows, and then calculate the maximum for each balancetype row value.
i.e.
PERIODBAL BALANCETYPE ID
0 ABS 1
15 ABS 1
11 ABS 1
-13 ABS 1
-16 ABS 1
20 ABS 1
SPLIT FOR ROW # 2
22 ABS 2
25 ABS 2
-78 ABS 2
0 ABS 2
1 ABS 2
......
FINALLY, FETCH MAX (PERIODBAL) and group data by balancetype, ID columns
CREATE TABLE SYS.TEST
(
PERIODBAL VARCHAR2(50 BYTE),
BALANCETYPE VARCHAR2(5 BYTE)
)
INSERT INTO TEST VALUES ('0;15;11;-13;-16;20', 'ABS');
INSERT INTO TEST VALUES ('22;25;-78;0;1', 'ABS');
INSERT INTO TEST VALUES ('67;89;-36;83;90;55 ', 'ABS');
INSERT INTO TEST VALUES ('0;15;10;-13;-16;23', 'ACS');
INSERT INTO TEST VALUES ('0;14;11;-13;-16;25', 'ACS');
decision:
SELECT BALANCETYPE,MAX(BAL) AS PERIODBAL
FROM
(
SELECT SUB1.*, TRIM(REGEXP_SUBSTR( PERIODBAL, '[^;]+', 1, LVL)) BAL
FROM (SELECT TEST.*, ROW_NUMBER() OVER (ORDER BY BALANCETYPE ) UNIQ_ID FROM TEST) SUB1 ,
(SELECT LEVEL LVL FROM DUAL,(SELECT MAX(LENGTH(REGEXP_REPLACE(PERIODBAL, '[^;]+')))+1 AS MAX_BAL FROM TEST) TEMP
CONNECT BY LEVEL<= TEMP.MAX_BAL ) SUB2
WHERE LENGTH(REGEXP_REPLACE(PERIODBAL, '[^;]+'))+1>= SUB2.LVL
) GROUP BY BALANCETYPE, UNIQ_ID
output:
BALANCETYPE PERIODBAL
ACS 23
ABS 20
ABS 25
ABS 90
ACS 25
Hope this helped, thanks.
+3
source to share
In MySQL, you can try
SELECT balanceType, MAX(CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(t.periodBal, ';', n.n), ';', -1), SIGNED INTEGER)) maxValue
FROM SomeData t CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(t.periodBal) - LENGTH(REPLACE(t.periodBal, ';', '')))
GROUP BY periodBal, balanceType
ORDER BY periodBal, maxValue;
See it in action: SQL Fiddle (with minor data changes to test some edge cases).
This is borrowed from comma separated SQL, line
But the oldest SO link for this approach I could find, Creating a MySQL SET from a line
0
source to share