POSTGRESQL : Using previous calculated value in WHERE clause

Question

Good evening! I have problem ,because I can't use MAXGROWTHPARENT value in WHERE and ORDER BY claused. I wrote this query:

    SELECT PERSON.*, 
(
    SELECT MAX(PARENT.GROWTH) 
    FROM MAN PARENT
    WHERE 
        (PARENT.ID = PERSON.ID_FATHER)
    OR
        (PARENT.ID = PERSON.ID_MOTHER)  
) AS MAXGROWTHPARENT
FROM MAN PERSON
WHERE PERSON.GROWTH > 
(
    SELECT MAX(PARENT.GROWTH) 
    FROM MAN PARENT
    WHERE 
        (PARENT.ID = PERSON.ID_FATHER)
    OR
        (PARENT.ID = PERSON.ID_MOTHER)
)
ORDER BY (PERSON.GROWTH - 
    (
        SELECT MAX(PARENT.GROWTH) 
        FROM MAN PARENT
        WHERE 
            (PARENT.ID = PERSON.ID_FATHER)
        OR
            (PARENT.ID = PERSON.ID_MOTHER)
    )
);

My code looks very ugly. Can you tell me how to use MAXGROWTHPARENT in WHERE and ORDER BY clauses?

Gordon Linoff · Accepted Answer · 2014-11-01 20:32:52Z

1

I think two separate joins might work better:

select p.*, greatest(pf.growth, pm.growth) as MAXGROWTHPARENT
from man p left join
     man pf
     on p.id_father = pf.id_father left join
     man pm
     on p.id_mother = pm.id_mother
order by p.growth - greatest(pf.growth, pm.growth);

Note: if the joins might not match up, you might prefer:

select p.*, greatest(coalesce(pf.growth, pm.growth), coalesce(pm.growth, pf.growth)) as MAXGROWTHPARENT
from man p left join
     man pf
     on p.id_father = pf.id_father left join
     man pm
     on p.id_mother = pm.id_mother
order by p.growth - greatest(coalesce(pf.growth, pm.growth), coalesce(pm.growth, pf.growth));

answered Nov 1, 2014 at 20:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

POSTGRESQL : Using previous calculated value in WHERE clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related