CODEWITHSUNDEEP
sqlpostgresqlmaxdistinct
Aaron
Aaron

Reputation: 4480

SQL getting distinct, max, and id

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 enter image description here

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

enter image description here

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

Upvotes: 1

Views: 320

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

If I understand correctly, you want distinct on:

SELECT DISTINCT ON (v.body), v.*
FROM versions v
WHERE v.body = 'Maintain records of all disclosures'
ORDER BY body, version DESC;

However, with only one row desired in the result set, LIMIT is sufficient:

SELECT v.*
FROM versions v
WHERE v.body = 'Maintain records of all disclosures'
ORDER BY body, version DESC
LIMIT 1;

Upvotes: 0

Hozeis
Hozeis

Reputation: 1742

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'

Upvotes: 1

trillion
trillion

Reputation: 1401

https://www.sqlservercentral.com/articles/eliminating-duplicate-rows-using-the-partition-by-clause

WITH DATA AS (

SELECT BODY, 
       ID,
       MAX(version) OVER(PARITION BY BODY ORDER BY ID) AS MAX_VERSION,
       ROW_NUMBER() OVER(PARTITION BY BODY ORDER BY ID) AS ROW_NUM

FROM versions
WHERE body = 'Maintain records of all disclosures'

)

SELECT 
*
FROM DATA
WHERE ROW_NUM = 1

Upvotes: 0

Related Questions