Select the maximum of field from different tables

Question

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.

score 1 · Answer 1 · answered Jun 30 '15 at 18:13

Ok, do you need to select only one date (most recent)? or do you need to select three dates (most recent of each)? I think that if you need onle the most recent date (just one date), you can use the query.

Let me show you an example (I don't know how to draw a table here, so..be patient):

Project table:
Id: 1
Number: 1
Name: One_Proy

Project_participant table:
ProjectID: 1
UserID: 1

User table:
Id: 1
UserName: lmessi
LasLogIn: Someday

Question table:
ProjectId: 1
LastUpdateOn: Yesterday

Document table:
ProjectId:1
LastUpdateOn: LastWeek

Instruction table:
ProjectId: 1
LastUpdateOn: Today

The query will return:
NUMBER: 1
NAME: One_Proy
LAST_LOGIN: Someday
MAX(LASTUPDATEON): Today

score 0 · Accepted Answer · answered Jun 29 '15 at 13:41

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.

Thanks Facundo La Rocca , but what is the difference. The Project Number ,Name and LastLogin is same right. — trx, Jun 29 '15 at 13:54
Now you have 3 dates for (number, name, last_login), the max(lastupdateon) returns what you need. Or maybe I'm not understanding your question... Sorry. You are trying to get the last update for (number, name, last_login). or am I wrong? — Facundo La Rocca, Jun 29 '15 at 19:06
Sorry for not being clear. I am trying to select a project's Name Number, Lastlogin and LastUpdatedOn depending on the UserId. But the LastUpdatedOn are present in three different tables Document,Question, and Instruction, we have to select the most recent LastUpdatedOn from these three tables for the ProjectId. — trx, Jun 30 '15 at 11:59

Select the maximum of field from different tables

2 Answers2