Help required for one sql query

I have below sql query and output. In this want only one record for each ProgramExecutionId which have latest time. Like in below screen shot for ProgramExecutionId 3, want only one record with max time 1-08-2019 00:00:00.


SyntaxEditor Code Snippet

SELECT DISTINCT {ActivityExecution}.[ProgramExecutionId],{ActivityExecution}.[PerformedOn]
               From {ActivityExecution},{ProgramExecution}
               WHERE {ActivityExecution}.[ProgramExecutionId]={ProgramExecution}.[Id]
               ORDER BY {ActivityExecution}.[PerformedOn] DESC
               
Solution

Hi Vikas,

You can use group by in this query to get only one record for ProgramExecutionId .

SELECT {ActivityExecution}.[ProgramExecutionId],max({ActivityExecution}.[PerformedOn])
               From {ActivityExecution},{ProgramExecution}
               WHERE {ActivityExecution}.[ProgramExecutionId]={ProgramExecution}.[Id]
GROUP BY {ActivityExecution}.[ProgramExecutionId],max({ActivityExecution}.[PerformedOn])
               ORDER BY {ActivityExecution}.[PerformedOn] DESC


Hope this will help you


Kind Regards

Rahul Sahu

Solution

use phrase limit 1