PostgreSQL: how do you SELECT DISTINCT relations and order by different fields depending on WHERE clause?

Each account is associated with one person and one type of account. I want to SELECT a distinct subset of accounts. In order t开发者_Go百科o be selected the accounts have meet at least one of two criteria. If an account occurs twice

I want to order this result set based on two different fields. This was my attempt:

Select DISTINCT a.*
FROM people AS p
JOIN accounts AS a
ON a.people_id = p.id
JOIN type_account AS t
ON t.type_id = a.id
WHERE t.id IN(1,3,5)
OR p.id IN(2,4,6)
ORDER BY(CASE
         WHEN p.id IN(2,4,6) THEN p.updated_at
         WHEN t.id IN(1,3,5) THEN p.created_at) AS position

And I got this error: SELECT DISTINCT, ORDER BY expressions must appear in select list

If I move the case statement to the select it possible for one account (associated with different people) to appear in the results twice, i.e. once when the first where clause is met and twice when the second where clause is met. In this case the accounts will be appearing twice in the result set.

I am having trouble wrapping my head around this one. Any help would be appreciated :)

Move your CASE statement(s) into the SELECT clause, then order on their position:

SELECT
    CASE
        WHEN p.id IN(2,4,6) THEN p.updated_at
        WHEN t.id IN(1,3,5) THEN p.created_at
    END AS position,
    DISTINCT a.*
FROM people AS p
JOIN accounts AS a
ON a.people_id = p.id
JOIN type_account AS t
ON t.type_id = a.id
WHERE t.id IN(1,3,5)
OR p.id IN(2,4,6)
ORDER BY 1 DESC
LIMIT 1;