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.
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,
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
Marcelo Ferreira wrote:
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
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]
Thank you Marcelo,
I have tried, but getting this error:
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!
Set forgot some parts
Inner join (SELECT MAX({Project}.[Version]) as version, {project}.[id] FROM {Project} group by {project}.[id] ) as latest_version
No, Marcelo Still doesn't work, It's giving all the result.
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]
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.