Double SELECT along with UNION and return the largest value, how?

Question

PostgreSQL

Good day , I've been
trying to make a correct request for 2 days.
The challenge is to double query the table to return the largest single value, but with UNION I can't seem to do it when double query returns 2 rows, but I need one row with the highest value of the "rate" column. The task is complicated by the fact that before comparing the "rate" field of 2 rows, it is necessary to multiply it for each row by the value n.
The request looks like this:

SELECT * FROM (
    SELECT rate, type, route_id, weight, volume,
      row_number() OVER (PARTITION BY route_id ORDER BY volume) as num
    FROM test_rates rs
    WHERE
      rs.dt_del IS NULL
      AND rs.volume >= 300  -- первая выборка
  UNION
    SELECT rate, type, route_id, weight, volume,
      row_number() OVER (PARTITION BY route_id ORDER BY weight) as num
    FROM test_rates rs
    WHERE
      rs.dt_del IS NULL
      AND rs.weight >= 50000  -- вторая выборка
) q1
WHERE q1.num = 1 -- возвращаем только 1 строку

The table itself is the following (open the result tab):

The 2 key columns are "weight" and "volume" for the first and second queries, respectively.
The result of the first query is like this (open the result tab):

Available commercially. I think we can do without union here, but I suppose with it we get the necessary lines and can work with them.

Answer 1

Subquery find MAX(rate)and query the row with this value.

Answer 2

It is possible like this:

SELECT rate, type, route_id, weight, volume FROM (
  SELECT rate, type, route_id, weight, volume,
      CASE WHEN rs.volume >= 300 THEN
        row_number() OVER (PARTITION BY route_id ORDER BY volume) 
      ELSE null END r_volume,
      CASE WHEN rs.weight >= 50000 THEN
        row_number() OVER (PARTITION BY route_id ORDER BY weight)
      ELSE null END r_weight
    FROM test_rates rs
    WHERE
      rs.dt_del IS NULL
      AND (rs.volume >= 300 OR rs.weight >= 50000)
    
) WHERE r_volume=1 OR r_weight=1
ORDER BY rate DESC
LIMIT 1

Answer 3

You can try with a temporary request:

WITH rates as
(SELECT rate, type, route_id, weight, volume
   FROM test_rates rs
  WHERE rs.dt_del IS NULL
    AND (rs.volume >= 300 OR rs.weight >= 50000))
SELECT rate, type, route_id, weight, volume
  FROM rates
 where rate = (select max(rate) from rates)