Skip to Content (Press Enter)
UserImage.jpg
vikas sharma
Champion
28Views
2Comments
Solved
Need help in one advanced sql query
Question

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]
0
0
31 Aug 2019
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP
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

See solution in context
1
0
31 Aug 2019
Copy Link
2025-04-30 22-40-10
Duarte Oliveira

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



1
0
31 Aug 2019
Copy Link
2021-09-06 15-09-53
Dorine Boudry
 
MVP
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

1
0
31 Aug 2019
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.