Is it possible to further filter on MAX results?

I have a table like so

A)

As you can see, the same ID can have multiple Revision IDs

Now, I can grab all the latest revision for each ID by using MAX with GROUP BY like so:

SELECT *, MAX(revision_id)
FROM content_moderation_state_field_revision
GROUP BY id DESC;

B)

As you can see here, the Revision ID of 47141 is the latest revision for ID 3282

So here is my goal

What I want to do is to further filter these (screenshot B) results down to only published Moderation States of the latest/highest revision.

However, if I try

SELECT *, MAX(revision_id)
FROM content_moderation_state_field_revision
WHERE moderation_state = 'published'
GROUP BY id DESC;

I get

C)

Which is not what I want, because it is no longer grabbing the latest/highest revision for each ID. 47139 is not the highest/latest revision for 3282, it’s 47141 as show in screenshot B.

What I want here is to only show the 3278 row because that’s the only row with also has the latest/highest revision that has a published moderation state (See screenshot B)

In a nutschell

I want to select only rows which are both ‘published’ and have the maximum revision_id

How can I achieve my goal? Is it possible?

Answer

You can use conditional logic along with a HAVING clause such as

SELECT id, 
       MAX(revision_id) AS revision_id,
       MAX(moderation_state) AS moderation_state
  FROM content_moderation_state_field_revision
 GROUP BY id
HAVING MAX(CASE WHEN moderation_state = 'published' THEN revision_id END) = 
       MAX(revision_id);