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.

CodePudding user response：

I tried to distinct on document_name but 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