list fields of latest version of each record-CodePudding

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.

CodePudding user response：

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.