0

I am trying to get the highest value from a blob and have the following

with q1 (key,fileformat,originatingchannelidx, submitterididx, "InitParty", "OIN", "Max_Value")
    AS (SELECT PINT.PAYMENTINTERCHANGEKEY, pint.fileformat,  pint.originatingchannelidx, pint.submitterididx, -- pint.referenceidx, 
XMLQuery('//*:GrpHdr/*:InitgPty/*:Nm/text()'
    passing XMLtype(pint.transportdata, nls_charset_id('AL32UTF8')) 
    returning content).getstringval()  "InitParty"
,
    XMLQuery('//*:GrpHdr/*:InitgPty/*:Id/*:OrgId/*:Othr/*:Id/text()'
passing XMLtype(transportdata, nls_charset_id('AL32UTF8')) 
    returning content).getstringval()  "OIN"
    ,
    to_number(XMLQuery('//*:GrpHdr/*:CtrlSum/text()' 
    passing XMLtype(pint.transportdata, nls_charset_id('AL32UTF8')) 
    returning content).getstringval()) "max_value"
from bph_owner.paymentinterchange pint
Where
pint.TRANSPORTTIME >= to_date('2022-08-14', 'yyyy-mm-dd') 
and pint.fileformat = 'pain.001.001.03'
--group by pint.fileformat, pint.transporttime, pint.originatingchannelidx, pint.submitterididx, pint.referenceidx
order by pint.submitterididx  --, max_sum
)
select  Q1.fileformat, q1.originatingchannelidx, q1.submitterididx, q1."InitParty", q1."OIN", q1."Max_Value",
    MAX("Max_Value") OVER (PARTITION BY q1."OIN") as Max_Sum
    from q1
    group by Q1.fileformat, q1.originatingchannelidx, q1.submitterididx, q1."InitParty", q1."OIN", q1."Max_Value"
Order by "InitParty", "OIN", "Max_Value"
;

which returns data

pain.001.001.03 BL  491360      491360  1874.42 1874.42

pain.001.001.03 BL  497955      497955  6764.88 6764.88

pain.001.001.03 BL  497986      497986  2831.82 2831.82

pain.001.001.03 BL  600639      600639  76593.87    76593.87

pain.001.001.03 BL  600913      600913  1500.59 1500.59

pain.001.001.03 BL  610049      610049  1060.62 1060.62

pain.001.001.03 BL  610570      610570  170395.53   **315108.52**

pain.001.001.03 BL  610570      610570  174741.98   **315108.52**

pain.001.001.03 BL  610570      610570  179224.99   **315108.52**

pain.001.001.03 BL  610570      610570  193430.66   **315108.52**

pain.001.001.03 BL  610570      610570  249188.51   **315108.52**

pain.001.001.03 BL  610570      610570  **315108.52**   **315108.52**

Anybody know how I can remove the rows apart from the highest value?

Vy Do
  • 46,709
  • 59
  • 215
  • 313
Peter warren
  • 11
  • 1
  • Does this answer your question? [GROUP BY with MAX(DATE)](https://stackoverflow.com/questions/3491329/group-by-with-maxdate) – astentx Aug 15 '22 at 09:18

1 Answers1

1

max(value) over () can return the max value of all rows. So you can wrap one more layer to add this as a filter.

...
select fileformat,originatingchannelidx,submitterididx,"InitParty","OIN","Max_Value",Max_Sum
from (
  select *, max(Max_Sum) over () as maxval
  from ( 
   select  Q1.fileformat, q1.originatingchannelidx, q1.submitterididx, 
      q1."InitParty", q1."OIN", q1."Max_Value",
       MAX("Max_Value") OVER (PARTITION BY q1."OIN") as Max_Sum,
    from q1
    group by Q1.fileformat, q1.originatingchannelidx, q1.submitterididx, 
      q1."InitParty", q1."OIN", q1."Max_Value"
    Order by "InitParty", "OIN", "Max_Value")
) where Max_Sum = maxval
;
WeDBA
  • 343
  • 4
  • 7