Get max/min value of the column independet from where clause-CodePudding

I am having the following query and running it on postgress

select 
    p.id as p_id,
    p.name as p_name,
    p.tags,
    p.creator,
    p.value
    p.creation_date,
    cp.id as c_part_id,
    fr.distance
    count(*) OVER() AS total_item
from t_p p 
    left join t_c_part cp on p.id = cp.p_id 
    left join t_fl fr on p.id = fr.p_id  
where p.name = 'test'
ORDER BY p.id ASC, p.name ASC 
OFFSET 0 FETCH NEXT 25 ROWS only

What is missing here is that I also need to get max(p.value) and min(p.value) not affected by the "where" clause - so calculated from total (all) values.

I am dreaming that I can do it within one query and reduce the number of transactions. Honestly not sure if it is possible!

What I tried is something like this ->

SELECT 
    (SELECT COUNT(*) from t_p) as count, 
    (SELECT json_agg(t.*) FROM (
        SELECT * FROM t_p
        where ***
    ) AS t) AS rows

But this one did not look really nice as it require additional JSON manipulation at the backend.

I discovered that I might try to use the "with" statement to create a temporary view so the where condition is only evaluated once, but did not succeed to make it works...

CodePudding user response：

I am not totally certain if that's what you are asking, but to add minimum and maximum to the output, you could use a window function to the SELECT list:

SELECT ...,
max(p.value) OVER (),
min(p.value) OVER ()
FROM ...

CodePudding user response：

You can join to a sub-query that calculates both MIN & MAX.

... 
from t_p p 
    left join t_c_part cp on p.id = cp.p_id 
    left join t_fl fr on p.id = fr.p_id 
    cross join (
      select 
        min(value) as min_value
      , max(value) as max_value 
      , avg(value) as avg_value
      from t_p
    ) as v
...

Then use v.min_value and v.max_value in the select.
Doesn't even have to be a LATERAL.

CodePudding user response：

You can add the extra columns as scalar subqueries in the form (select min(value) from t_p). Their values are not related to the main query so they should be totally independent.

Your original query has some minor syntax issues (missing commas). I fixed those and the result is:

select 
    p.id as p_id,
    p.name as p_name,
    p.tags,
    p.creator,
    p.value,
    p.creation_date,
    cp.p_id as c_part_id,
    fr.distance,
    count(*) OVER() AS total_item,
    (select min(value) from t_p) as min_value,
    (select max(value) from t_p) as max_value
from t_p p 
    left join t_c_part cp on p.id = cp.p_id 
    left join t_fl fr on p.id = fr.p_id  
where p.name = 'test'
ORDER BY p.id ASC, p.name ASC 
OFFSET 0 FETCH NEXT 25 ROWS only

See running query (without any data) at DB Fiddle.