i have below data coming db. maximum of last digits after ".". example data looks this, last digits after last "." 160410, 6, 16 etc.
i "11.2.0.4.160419" output
11.2.0.4.160419 11.2.0.4.6 11.2.0.4.16 11.2.0.4.10 11.2.0.4.18 11.2.0.4.2 11.2.0.4.14 11.2.0.4.4 11.2.0.4.160119 11.2.0.4.3 11.2.0.4.15 11.2.0.4.9 11.2.0.4.17 11.2.0.4.8 11.2.0.4.5 11.2.0.4.7 11.2.0.4.1 11.2.0.4.151117 11.2.0.4.13 11.2.0.4.12 11.2.0.4.20 11.2.0.4.11 11.2.0.4.19 data before "." not same. has various values. infact actual data
database patch exadata (jan 2016 - 11.2.0.4.160119) : (22309110) database patch exadata (jan 2016 - 11.2.0.4.16) : (22309111) . . in interested max of 160119.
-- added
sorry again. looking further need result this
11.2.0.4.160419
meaning, maximum of after "." , when displaying display in between parenthesis.
actual data
'database patch exadata (nov 2015 - 11.2.0.4.151117)
database patch exadata (apr2014 - 11.2.0.4.6) : (18293775)
database patch exadata (apr2015 - 11.2.0.4.16) : (20449729)
desired output
(nov 2015 - 11.2.0.4.151117)
i have query working
with
inputs ( target_guid, description) (
select t.target_guid, a.description mgmt$oh_patch a, mgmt$oh_installed_targets oh,mgmt$target_components c,mgmt$target_flat_members d, mgmt_targets t t.target_type = 'oracle_dbmachine' , d.member_target_type = 'host' , d.aggregate_target_guid = t.target_guid , c.target_type = 'oracle_database' , c.host_name = d.member_target_name , a.host_name = c.host_name , a.target_guid = oh.oh_target_guid , oh.inst_target_type '%database%' , a.description not null , a.description '%patch exadata%' group t.target_guid, a.description order t.target_guid )
select target_guid, max(to_number(regexp_substr(description, '.(\d*))', 1, 1, null, 1))) version
inputs group target_guid;
output of
5da0496cccd42ca1099f1ad06216f3c0 160419
ed10dd7d4c62ceaa117e7b7e97883ec2 9
i need output as
5da0496cccd42ca1099f1ad06216f3c0 11.2.0.4.160419 ed10dd7d4c62ceaa117e7b7e97883ec2 11.2.0.4.9
can please help?
if assumptions detailed in comment original question correct, should work:
with inputs ( inp_str ) ( select 'database patch exadata (jan 2016 - 11.2.0.4.160119) : (22309110)' dual union select 'database patch exadata (jan 2016 - 11.2.0.4.16) : (22309111)' dual ) select max(to_number(regexp_substr(inp_str, '.(\d*)\)', 1, 1, null, 1))) max_something inputs; the select statement last 2 lines; rest testing purposes. replace inp_str actual column name, inputs table name, , max_something desired output column name.
edit:
here solution op's restated problem (see request in comments).
with inputs ( inp_str ) ( select 'database patch exadata (jan 2016 - 11.2.0.4.160119) : (22309110)' dual union select 'database patch exadata (jan 2016 - 11.2.0.4.16) : (22309111)' dual ) select regexp_substr(inp_str, '\(([^)]+)\)', 1, 1, null, 1) token inputs to_number(regexp_substr(inp_str, '.(\d*)\)', 1, 1, null, 1)) = ( select max(to_number(regexp_substr(inp_str, '.(\d*)\)', 1, 1, null, 1))) inputs ) ; output:
token ------------------------- jan 2016 - 11.2.0.4.160119 1 row selected. 
Comments
Post a Comment