How to fetch a column name which has maximum value from each group in postgres?

I got stack when i try to return maximum value in this query. Here is my query

SELECT count(app.product_service_id) AS app_service_id,
       ps.product_service_name,
       CAST(extract('month'
                    FROM to_date(app_date, 'DD/MM/YYYY')) AS INTEGER) AS months,
       CAST(extract('year'
                    FROM to_date(app_date, 'DD/MM/YYYY')) AS INTEGER) AS years,
       CONCAT(extract('year'
                      FROM to_date(app_date, 'DD/MM/YYYY')), ' ', to_char(to_date(app_date, 'DD/MM/YYYY'), 'Month')) AS month_name
FROM appointment app
JOIN product_services ps ON app.product_service_id = ps.product_service_id
WHERE to_date(app_date, 'DD/MM/YYYY') > to_date('01-07-2019', 'DD/MM/YYYY')
  AND to_date(app_date, 'DD/MM/YYYY') < to_date('01-07-2020', 'DD/MM/YYYY')
GROUP BY product_service_name,
         month_name,
         years,
         months
ORDER BY years,
         months

It is showing me the output like this according the query

But i want the maximum product_service_name. my expected output is

I tried with max(ps.product_service_name) but it did fetch unexpected result. it will be really helpful if anyone help me to solve this

Answer

You can use row_number. If I understand correctly you want to have result with max(app_service_id) by month.

with cte as
(
  SELECT 
    count(app.product_service_id) AS app_service_id,
    ps.product_service_name,
    CAST(extract('month' FROM to_date(app_date, 'DD/MM/YYYY')) AS INTEGER) AS months,
    CAST(extract('year' FROM to_date(app_date, 'DD/MM/YYYY')) AS INTEGER) AS years,
    CONCAT(extract('year' FROM to_date(app_date, 'DD/MM/YYYY')), ' ', to_char(to_date(app_date, 'DD/MM/YYYY'), 'Month')) AS month_name
  FROM appointment app
  JOIN product_services ps 
  ON app.product_service_id = ps.product_service_id
  WHERE to_date(app_date, 'DD/MM/YYYY') > to_date('01-07-2019', 'DD/MM/YYYY')
    AND to_date(app_date, 'DD/MM/YYYY') < to_date('01-07-2020', 'DD/MM/YYYY')
  GROUP BY 
    product_service_name,
    month_name,
    years,
    months
)

select
  app_service_id,
  product_service_name,
  months,
  years,
  month_name
from
(
  select
      *,
      row_number() over(partition by months order by app_service_id desc) as rn
  from cte
) subq
where rn = 1