0

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
1
2

(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.

1
  • It seems to me that FILTER is by far the superior way of doing this (I was experimenting with this last night). If you want to test your answers, you can always use the excellent dbfiddle.uk site. – Vérace Jun 6 '20 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.