Is it possible to use MAX with a condition?

Question

I have a jobs table that has two relevant fields date_executed and user_id. I was wondering if there's a way I can avoid joins or inline sql and just calculate both the last executed time, and the last executed time for a specific user in one simple query. I want to bee able to use MAX on everything, and a MAX on a subset of the results.

Example mock-up query. Line 3 has made-up syntax that's hopefully self-explanatory for what I want to do:

SELECT
    MAX(date_executed) as last_executed,
    MAX(date_executed, user_id = 'current-user-id') as last_executed_by_user
FROM
    jobs;
GROUP BY
    some_other_field

Answer 1

(I'm a bit rusty with PostgreSQL, but I believe the query below is ISO-compliant and so should work):

SELECT
    MAX( date_executed) as last_executed,
    MAX( CASE WHEN user_id = $userId THEN date_executed END ) AS last_executed_by_user
FROM
    jobs;
GROUP BY
    some_other_field

• $userId is a parameter you'll specify in your PostgreSQL client library.
• A CASE WHEN x THEN y END expression without an ELSE case will evaluate to NULL when x is false or Unknown.
• The MAX aggregate function only considers non-NULL values, therefore it only considers values in jobs.date_executed when jobs.user_id = $userId.

You can also use the FILTER clause to get the same result (thanks for the tip, ypercubeᵀᴹ!):

SELECT
    MAX( date_executed) AS last_executed,
    MAX( date_executed ) FILTER ( WHERE user_id = $userId ) AS last_executed_by_user
FROM
    jobs;
GROUP BY
    some_other_field

Both queries will return last_executed_by_user = NULL if there are no rows where user_id = $userId.