Change color of a chart in oracle apex if its a maximal value

I'm creating a bar chart in Oracle Apex 23ai and I need to change the color of bars from selected rows when they have the max value. My label is X_LABEL, value is Y_VALUE and X_VAL is used to sort the bars according to day name (in Polish).

I have been able to make bars a different color based on a text value of X_LABEL in another chart:

SELECT A.*,
    case X_LABEL 
        when 'DZIEŃ WOLNY' THEN '#5F7D4F'
        WHEN 'DZIEŃ PRACUJĄCY' THEN '#CA4D3C'
    end bar_colour 
FROM (
    SELECT 'DZIEŃ PRACUJĄCY' AS X_LABEL, COUNT(WORKING_DAY) AS Y_VALUE FROM HEADACHE_RECORDS WHERE WORKING_DAY = 1
    UNION
    SELECT 'DZIEŃ WOLNY' AS X_LABEL, COUNT(WORKING_DAY) AS Y_VALUE FROM HEADACHE_RECORDS WHERE WORKING_DAY = 0
) A

But in the chart I'm creating I need to mark bars with the highes value.

SELECT
    A.*
FROM (
    SELECT 'PONIEDZIAŁEK' AS X_LABEL, 1 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 1 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'WTOREK' AS X_LABEL, 2 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 2 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'ŚRODA' AS X_LABEL, 3 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 3 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'CZWARTEK' AS X_LABEL, 4 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 4 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'PIĄTEK' AS X_LABEL, 5 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 5 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'SOBOTA' AS X_LABEL, 6 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 6 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'NIEDZIELA' AS X_LABEL, 7 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 7 AND HEADACHE_TYPE IS NOT NULL
    ORDER BY X_VAL
) A

Moreover, sometimes more than one colum has the max value, as shown here: screenshot

Solution

Here is a solution based on the sample charts app that can be installed from the gallery. On page 9 there is a region called SQL Series-Defined Colors. That is the technique you're using. Use the RANK() in a case statement to set the color of the highest value. If there are rows with the same rank they will get the same number.

select a.product_name, 
       b.quantity, 
       b.customer,       
       case when RANK() OVER (PARTITION BY 1 ORDER BY b.quantity) = 1 then 'pink'
            when b.quantity > 50 then 'gold'
            when b.quantity <= 30 then 'red' 
         when b.quantity > 30 then 'green'
         else 'blue'
       end colors 
from eba_demo_chart_products a, eba_demo_chart_orders b
where a.product_id = b.product_id
and customer = 'Store A'
order by a.product_name asc

For your data that would be something like

SELECT A.*,
    CASE WHEN  RANK() OVER (PARTITION BY 1 ORDER BY Y_VALUE DESC) = 1 then 'pink'
    ELSE
        CASE X_LABEL 
            WHEN 'DZIEŃ WOLNY' THEN '#5F7D4F'
            WHEN 'DZIEŃ PRACUJĄCY' THEN '#CA4D3C'
        END 
    END bar_colour 
FROM (
    SELECT 'DZIEŃ PRACUJĄCY' AS X_LABEL, COUNT(WORKING_DAY) AS Y_VALUE FROM HEADACHE_RECORDS WHERE WORKING_DAY = 1
    UNION
    SELECT 'DZIEŃ WOLNY' AS X_LABEL, COUNT(WORKING_DAY) AS Y_VALUE FROM HEADACHE_RECORDS WHERE WORKING_DAY = 0
) A
;