I have two tables like below for versioning,
application table only stores static fields (fields that will never change, in this case only the id of application). And application_version table stores the dynamic fields (fields that might change in the future, like it can be renamed etc.) and every time there is a change, a new row is being added to the application_version table. (Here is a more detailed explanation of this type of versioning design.)
application
--------------------
id (PK)
created_at
...
application_version
--------------------
id (PK)
name
description
version
application_id (FK)
...
So i’d like to list name, description etc. of all the applications last version.
I’m just able to list max versions of each application:
SELECT max(version) FROM application_version GROUP BY application_id
How do i make the mentioned query.
>Solution :
In PostgreSQL you can use DISTINCT ON. DISTINCT ON keeps the first row for each grouping, according to the ORDER BY clause.
For example:
select distinct on (application_id) *
from application_version
order by application_id, version desc
See running example at db<>fiddle.