I am new at sql queries and there is a case that i couldnt handle. Lets assume i have a table like below;
id | document_name | country | major_version | minor_version |
1 | policy1 | DE | 1 | 0 |
2 | policy2 | DE | 1 | 0 |
3 | policy1 | DE | 1 | 1 |
4 | policy1 | DE | 2 | 0 |
5 | policy2 | DE | 1 | 1 |
6 | policy2 | IT | 1 | 0 |
7 | policy2 | IT | 1 | 1 |
And i would like to return only;
id | document_name | country | major_version | minor_version |
4 | policy1 | DE | 2 | 0 |
5 | policy2 | DE | 1 | 1
|
The latest version of document where country is 'DE'. What kind of query i should run?
I tried to distinct on document_name but then it didn't return the latest version of document.
I tried to
distinct on document_namebut then it didn't return the latest version of document.
distinct on is a good approach for this in Postgres, if you use the proper order by clause:
select distinct on (document_name) t.*
from mytable t
order by document_name, major_version desc, minor_version desc