I have a column like that :
How can I find the max digit column? ( I want to select the max digit from the left column and write it to a new column)
WITH ORACLE SQL
THANKS!!!
5 Answers
Solving it like a pro:
Creating sample data:
CREATE TABLE tab as
WITH t(col) AS
(
SELECT 134425 FROM dual UNION ALL
SELECT 6453356 FROM dual UNION ALL
SELECT 65267 FROM dual UNION ALL
SELECT 9999 FROM dual UNION ALL
SELECT 467533 FROM dual
)
SELECT * FROM t;
Solution:
select * from tab,
lateral
(
select max(substr(col, level, 1)) max_digit
from dual
connect by level <= length(col)
)
Output:
2 Comments
determinator
that works like a charm! thank you bro! you made my day!
Ranagal
Glad I could help.
You can use a brute force method as well:
select (case when column like '%9%' then 9
when column like '%8%' then 8
. . .
end)
I mention this because a recursive query is not necessarily needed for this.
Comments
One method uses a recursive query to split the number into digits. A recursive query comes handy for this:
with cte (col, val, pos, maxpos) as (
select col, substr(col, 1, 1), 1, length(col) from mytable
union all
select col, substr(col, pos + 1, 1), pos + 1, maxpos from cte where pos < maxpos
)
select col, max(val) max_digit
from cte
group by col
COL | MAX_DIGIT ------: | :-------- 6453356 | 6 9999 | 9 65267 | 7 467533 | 7 134425 | 5
2 Comments
determinator
could you solve this by "create table" not "with" , I do not understand how it works. thank you so much!
GMB
@determinator: the cte puts each digit in a separate row. You change the outer query to
select * from cte order by col, pos to see what it returns and understand the logic.One option would be using Hierarchical query along with REGEXP_SUBSTR() function in order to split each digits individually first, and then find the maximum digit values for each grouped column values :
SELECT col, MAX( REGEXP_SUBSTR(col,'[^\d]',level) ) AS maxdigit
FROM t
CONNECT BY level <= LENGTH(col)
AND PRIOR SYS_GUID() IS NOT NULL
AND PRIOR col = col
GROUP BY col
Comments
but you can do it much easier and shorter: without any subqueries, CTE and connect-by.
just using translate, length and rtrim:
length(
rtrim(
translate(
translate('0123456789',your_number,' ')
,' 0123456789'
,'x '
)))-1 as max_number
Step-by-step description:
translate('0123456789',x,' ')removes from '0123456789' existing digits. Lets call itremove_existingtranslate(remove_existing, ,' 0123456789','x ')replaces spaces in remove_existing with 'x'. So you by position of X which digits exist in your number, for example 'x x x x xx' means that you have '0 2 4 6 89' in your number. Let's call itexisting_as_xlength(rtrim(existing_as_x))-1removes spaces from right and returns length, so returned length-1 is equal to your required maximum digit.
Full test case:
with t(x) as (
select * from table(sys.odcinumberlist(
134425,
6453356,
65267,
9999,
467533
))
)
select
x,
translate('0123456789',x,' ') remove_existing,
translate(
translate('0123456789',x,' ')
,' 0123456789','x '
) as existing_as_x,
length(
rtrim(
translate(
translate('0123456789',x,' ')
,' 0123456789'
,'x '
)))-1 as max_number
from t;
Results:
X REMOVE_EXISTING EXISTING_AS_X MAX_NUMBER
---------- ---------------- ------------- ----------
134425 0 6789 xxxxx 5
6453356 012 789 xxxx 6
65267 01 34 89 x xxx 7
9999 012345678 x 9
467533 012 89 xxxxx 7
2 Comments
Ranagal
Oh, that's really nice. !! I must thank you for thinking extra and coming up with a novel approach. I guess, it must be the fastest of all the solutions offered so far. Only testing can prove it, though.
Sayan Malakshinov
I'm pretty sure that this one is a fastest solution from all posted as for now, but you can check it on
select level x from dual connect by level<=1e6 - just sum maximum digits on such generated data set (1mln rows)
translatesolution?