SQL getting distinct, max, and id

Question

I have a table called versions and I am trying to get the id, the body, and the highest version. I am using Postgres

When I use

SELECT DISTINCT(body), MAX(version) FROM versions
WHERE body = 'Maintain records of all disclosures'
GROUP BY body

I get

When I try to add id

SELECT DISTINCT(body), MAX(version), id  FROM versions
WHERE body = 'Maintain records of all disclosures'
GROUP BY body, id

I get

What do I need to change so I am only getting the id where max is the highest?

Answer 1

You could do something like this:

SELECT body, version, id 
FROM versions 
WHERE version = (
 SELECT MAX(version) 
  FROM versions 
  WHERE body = 'Maintain records of all disclosures') 
AND body = 'Maintain records of all disclosures'