I am trying to retrieve the last Job for parts in selected product groups and am using Create date and a calculated (Row number) row_number() over(partition by JobHead_lastJob.JobNum order by JobHead_lastJob.CreateDate desc)in a sub-query on the Job Head .
In the top level I have the criteria calculated row = 1. But it is returning all of the jobs.
In some cases there are 6 - 7 jobs for the same part for the same day and I just one 1 record.
Is there another way to achiever this?
Open to suggestions.
Thank you all.
Carol