maximo

Maximo Query showing greater value of two columns using coalesce


End-user of Maximo here. Trying to write a query to sort two columns, named Time% and Meter%, no less than 95%, by the greatest of either column in descending order.

Here is my query:

((exists (select 1 from maximo.asset where ((location like '%RDU%')) and (assetnum = pm.assetnum and siteid=pm.siteid) and (plustisconsist=0)))) and ((fxf_meterpercent > 95.0 or fxf_timepercent > 95.0)) order by GREATEST( COALESCE(fxf_timepercent, 0), COALESCE(fxf_meterpercent, 0) )

Here is the result, as you can see it is sorting in ASC, but I would like it to be in descending.

Screenshot


Solution

  • You add desc at end of SQL query for descending order. Here sample

    ((exists (select 1 from maximo.asset where ((location like '%RDU%')) and (assetnum = pm.assetnum and siteid=pm.siteid) and (plustisconsist=0)))) and ((fxf_meterpercent > 95.0 or fxf_timepercent > 95.0)) order by GREATEST( COALESCE(fxf_timepercent, 0),   COALESCE(fxf_meterpercent, 0) ) desc