Search code examples
outsystems

Select the maximum of field from different tables

I tried to run the query without selecting the max of LastUpdatedOn section. It works. But I am not sure how do I select the Maximum of LastUpdatedOn from the Document,Instruction,Question for the particular Project Id.

SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],
(
SELECT MAX(LastUpdatedOn) AS max_LastUpdatedOn
FROM
(
SELECT {Question}.[LastUpdatedOn]
UNION ALL
SELECT {Document}.[LastUpdatedOn]
UNION ALL
SELECT {Instruction}.[LastUpdatedOn]
) A
)[max_LastUpdatedOn]

From {Project}

INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
INNER JOIN {User} ON ({ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId)
INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]
INNER JOIN {Instruction} ON {Project}.[Id] = {Instruction}.[ProjectId]
INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]

GROUP BY {Project}.[Number],
         {Project}.[Name],
         {User}.[Last_Login],

ORDER BY {Project}.[Number]

I am getting the below error Column 'PORTAL.OSUSR_E2R_QUESTIONS_T9.LASTUPDATEDON' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Solution

  • I think that should split your query in 3 sub queries for each "LastUpdateOn". So you take first: {Project}.[Number],{Project}.[Name],{User}.[Last_Login], {Document}.[LastUpdatedOn]

    Second: {Project}.[Number],{Project}.[Name],{User}.[Last_Login], {Instruction}.[LastUpdatedOn]

    Finish: {Project}.[Number],{Project}.[Name],{User}.[Last_Login], {Question}.[LastUpdatedOn]

    Now you can do:

    SELECT RESULT.NUMBER, RESULT.NAME, RESULT.LAST_LOGIN, MAX(RESULT.LASTUPDATEON) 
  FROM (SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login], {Question}.[LastUpdateOn] AS LastUpdateOn 
          FROM {Project}
         INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
         INNER JOIN {User} ON ({ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId)
         INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]
         UNION
        SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login], {Document}.[LastUpdateOn] AS LastUpdateOn
          FROM {Project}
         INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
         INNER JOIN {User} ON ({ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId)
         INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]
         UNION
        SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login], {Instruction}.[LastUpdateOn] AS LastUpdateOn
          FROM {Project}
         INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
         INNER JOIN {User} ON ({ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId)
     INNER JOIN {Instruction} ON {Project}.[Id] = {Instruction}.[ProjectId]) AS RESULT 
GROUP BY RESULT.NUMBER, RESULT.NAME, RESULT.LAST_LOGIN 
ORDER BY RESULT.NUMBER

    I think that it should work.