Get latest version of record from database after each saves

Get latest version of record from database after each saves

  

I have below table (will be with the joining of others entities too).
Here, each time new entry of a project will get stored in DB after each saves. Each time ProjectVersion++.

And I want the latest entry by ProjectVersion.


Can I achieve this by using Aggregates? How?
If no, Can anyone please help with short SQL.

I also want to use this to show in Datatable with searching and sorting functionality, generated by scaffolding. So search and sort variables should also work.

I tried with the aggregate group by and max, but for this, I need to group all the columns, and not possible this on other fields like ProjectTitle and Description, because it can be different each time.

 ProjectId  ProjectVersion  ProjectNo ProjectTitle  Description 
1010  XXXTestAdded description
1021  XXXTestUpdated description
1030  YYYTest 2 ProjectAdded description
1041  YYYTest 2 ProjectUpdated description
1052  YYYTest 2 Project  updatedUpdated again



I tried below query But doesn't work.

SyntaxEditor Code Snippet

SELECT {Project}.[Version], {Project}.[ProjectNo], {Project}.[Desc], {Project}.[Status],etc...
FROM {Project} 
    LEFT JOIN .......................
 WHERE (  {Project}.[JobStatus] = @ProjectStatusId) => for searching through search filter..................
-- {Project}.[Version] = (SELECT MAX({Project}.[Version]) FROM {Project} AS Project_2 where Project_2.Id = {Project}.[Id])


Please suggest a better idea for this!

Hi Amol

Did you try with a inner join?

FROM 

{Project} 

Inner join (SELECT MAX({Project}.[Version]), {project}.[id] FROM {Project}) as latest_version

ON latest_version.id ={Project} .[id]

Regards,

Marcelo

Hi,


When we insert/Update outsystems action return the id of that record,On the basis of id u can filter aggregate once again after insert/update it will return record,From record u can read version.


Thanks!

Marcelo Ferreira wrote:

Hi Amol

Did you try with a inner join?

FROM 

{Project} 

Inner join (SELECT MAX({Project}.[Version]), {project}.[id] FROM {Project}) as latest_version

ON latest_version.id ={Project} .[id]

Regards,

Marcelo

Thank you Marcelo,

I have tried, but getting this error:


Hi,

Set forgot some parts

FROM 

{Project} 

Inner join (SELECT MAX({Project}.[Version]) as version, {project}.[id] FROM {Project} group by {project}.[id] ) as latest_version

ON latest_version.id ={Project} .[id]

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi,

Set forgot some parts

FROM 

{Project} 

Inner join (SELECT MAX({Project}.[Version]) as version, {project}.[id] FROM {Project} group by {project}.[id] ) as latest_version

ON latest_version.id ={Project} .[id]

Regards,

Marcelo


No, Marcelo Still doesn't work, It's giving all the result.

SyntaxEditor Code Snippet

SELECT  {Projects}.* FROM {Projects} 
INNER JOIN (SELECT MAX({Projects}.[Version]) as Version, {Projects}.[ProjectNo] as ProjectNo  FROM {Projects} 
GROUP BY {Projects}.[ProjectNo] ) AS LatestVersion ON LatestVersion.ProjectNo = {Projects}.[ProjectNo]


Solution

Hi,

SELECT  {Projects}.* FROM {Projects} 
INNER JOIN (SELECT MAX({Projects}.[Version]) as Version, {Projects}.[ProjectNo] as ProjectNo  FROM {Projects} 
GROUP BY {Projects}.[ProjectNo] ) AS LatestVersion ON LatestVersion.ProjectNo = {Projects}.[ProjectNo] and LatestVersion.Version = {Projects}.[ProjectVersion]

Regards,

Marcelo

Solution

Marcelo Ferreira wrote:

Hi,

SELECT  {Projects}.* FROM {Projects} 
INNER JOIN (SELECT MAX({Projects}.[Version]) as Version, {Projects}.[ProjectNo] as ProjectNo  FROM {Projects} 
GROUP BY {Projects}.[ProjectNo] ) AS LatestVersion ON LatestVersion.ProjectNo = {Projects}.[ProjectNo] and LatestVersion.Version = {Projects}.[ProjectVersion]

Regards,

Marcelo


Hey Marcelo, Thanks for your efforts, I got what I want.


But still let me know, Isn't it possible through an aggregate? So that we can achieve OS optimizations. 

I tried But need to group all the columns and also mentioned a few other things in my questions.
So, Please suggest. 

Hi Amol,

The only way to achieve this is through a inner query and that is not possible in aggregates.

Lets hope they find a way of doing this on aggregates in the future.

Regards,

Marcelo