OR statement in select part of query in Postgres

Question

I have a query

SELECT 
        cd.signoffdate,
        min(cmp.dsignoff) as dsignoff
      FROM clients AS c 
      LEFT JOIN campaigns AS cmp ORDER BY dsignoff;

If I want to have something like this built into the postgres query will it work and how do I do it

if the cd.signoffdate is empty it should take min(cmp.dsignoff) as dsignoff as the value and then order by this column, so in other words it should order by dsignoff and cd.signoffdate and tread it as one column, is this possible and how?

Erwin Brandstetter · Accepted Answer · 2012-05-08 21:39:32Z

2

Your query could look like this:

SELECT c.client_id, COALESCE(c.signoffdate, min(cmp.dsignoff)) AS signoff
FROM   clients c 
LEFT   JOIN campaigns cmp ON cmp.client_id = c.client_id  -- join condition!
GROUP  BY c.client_id, cd.signoffdate                     -- group by!
ORDER  BY COALESCE(c.signoffdate, min(cmp.dsignoff));

Or, with simplified syntax:

SELECT c.client_id, COALESCE(c.signoffdate, min(cmp.dsignoff)) AS signoff
FROM   clients c 
LEFT   JOIN campaigns cmp USING (client_id)
GROUP  BY 1, cd.signoffdate
ORDER  BY 2;

Major points:

Used alias c, but referenced as cd.
No join condition leads to cross join, probably not intended.
Missing GROUP BY.
I assume that you want to group by the primary key column of clients and call it client_id.
I also assume that client_id links the two tables together.
COALESCE() serves as fallback in case signoffdate IS NULL.

edited May 8, 2012 at 21:39

answered Dec 9, 2011 at 8:44

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

Comments

Thilo · Accepted Answer · 2011-12-09 08:09:09Z

0

  ORDER BY coalesce(cd.signoffdate, min(cmp.dsignoff));

But don't you need some GROUP BY in your original query?

answered Dec 9, 2011 at 8:09

Thilo

264k107 gold badges527 silver badges674 bronze badges

Comments

Thong Kuah · Accepted Answer · 2011-12-09 08:14:04Z

0

You can use COALESCE

SELECT COALESCE(cd.signoffdate, min(cmp.dsignoff)) as dsignoff

I'm not sure if you can order by coalesce in Postgres - might be worth just ordering by both columns

answered Dec 9, 2011 at 8:14

Thong Kuah

3,2832 gold badges21 silver badges29 bronze badges

Collectives™ on Stack Overflow

OR statement in select part of query in Postgres

3 Answers 3

Major points:

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Major points:

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related