Need help in one advanced sql query

I wrote below SQL query 

in this for each ProgramExecutionId, have more than one performedOn records. I want to show only "one record with latest time for each programExecutionId. Like in below screen shot, ExecutionId is 3, so want to show only one entry with latest performedOn for this 3. Please suggest.


SyntaxEditor Code Snippet


SELECT {User}.[Name],{Program}.[Name],{ActivityExecution}.[PerformedOn], {ActivityExecution}.[ProgramExecutionId]
FROM {User}, {UserStore},{PackageProgram},{ProgramExecution},{Program},{ActivityExecution}
Where {UserStore}.[PackageId]={PackageProgram}.[PackageId] and 
     {PackageProgram}.[ProgramId]={ProgramExecution}.[ProgramId] and
     {User}.[Id] = {ProgramExecution}.[UserId]
     and {ProgramExecution}.[ProgramId]={Program}.[Id]
     and {ActivityExecution}.[ProgramExecutionId]={ProgramExecution}.[Id]

Hi Vikas,

Try to use an aggregator, it is easier:

 1. Add all required tables, 

 2. group by the columns {User}.[Name],{Program}.[Name], {ActivityExecution}.[ProgramExecutionId]

 3. add a max over {ActivityExecution}.[PerformedOn] 


Regards,

Duarte



Solution

Hi Vikas,

you could add a group by clause on the 3 other columns, and select the max() value of the PerformedOn column


As far as I can tell, with the current set of query result fields, you don't need to involve neither PackageProgram nor UserStore, so i removed them from the query as well.  

If you would want to involve them later because you want some info from them, you would have to decide for each column you add whether you want to incorporate it into the group by clause, or add some aggregate function (like max or count) on it.  


would look a bit like this : (sorry, I rearranged your query a bit to make sure i understand it)  

SELECT   {User}.[Name],
         {Program}.[Name],
         MAX({ActivityExecution}.[PerformedOn]),
         {ActivityExecution}.[ProgramExecutionId]
FROM     {User}, {ProgramExecution},{Program},{ActivityExecution}
Where    {User}.[Id] = {ProgramExecution}.[UserId]
and      {ProgramExecution}.[ProgramId]={Program}.[Id]
and      {ActivityExecution}.[ProgramExecutionId]={ProgramExecution}.[Id]
group by {User}.[Name],
         {Program}.[Name],
         {ActivityExecution}.[ProgramExecutionId]


Hope this helps,

Dorine

Solution