Most recent records

[Solo4357]

I have a table with various info in it for jobs. The table adds records sometimes for the month if the forecasting changes. Sometime nothing gets added that month. So a job will sometimes have many records, sometimes only 1. I need the most recent record only.

columns:
PKSEQ PK Int
PeriodDate datetime (always the first of the month, only one record per month.)
JobNumber varchar
budget number fields (dec, several of these)

I do not need to keep this primary key structure. I could for instance make jobcode and perioddate the pk's but I'm not sure which is the best way to go for what I'm trying to do.... thanks in advance!

 

[Solo4357]

Got it... I was foolishly linking on the primary key rather than the job code. But for anyone who wants a sample:

SELECT    * FROM WIP AS W1
 INNER JOIN                          (SELECT     JOBCODE, MAX(PeriodDate) AS PeriodDate
                            FROM          WIP
                            GROUP BY JOBCODE) AS W2 ON W1.JOBCODE = W2.JOBCODE AND W1.PeriodDate = W2.PeriodDate