I have postgresql db where i keep data for jee application. User can edit those data without creating new version of entity(draft). During edit, entity is copied as draft(version set to -1) after editing user can save all changes as new version. My problem is to create proper sql query to get data by version instead of extract all of them and do it in application.
id is uniq value and automatically incremented. When user start editing new field is created with field_id=parent.id. So i can extract all versions of field using group by field_id
I want to get current version which means max value of version field or version set to -1.
I want to get specific version. It is not that easy because data in db are not changed if field was not edited(to save space). For example field a was created with version 1 and then changes was done on other fields. Current global version is 7 but field a still have one entry in db with version=1(this field was not edited). So to extract specific version different than current i need to get MAX(version) where version<=:selected_version
id(uniq),field_id,version 1,1,1 3,1,7 55,1,-1 53,53,14
Entity with field_id=1 has 2 version and draft(user is editing it).
Entity with field_id=53 has one version and no draft.
I hope that my explanation of problem is understandable. Could you help me with those sql query? It could be two queries, one for actual version with draft and other for old versions.
Case 2 probably solution
I think that specific version i can extract using such query
select field_id, max(version) ver from field_mappings where version<4 and version!='-1' group by field_id
I don't know why i can't extract id. When i change query to
select *, max(version) from field_mappings where version<4 and version!='-1' group by field_id
Then i got error
ERROR: column "field_mappings.id" must appear in the GROUP BY clause or be used in an aggregate function SQL: 42803
And still don't know how to add case for draft(max(version) or version=-1).