Remove duplicate but keep the latest record

[Compufreak]

Hi everyone

im having some trouble with this query.

SELECT distinct  [CLUSTER]

 
----      ,[UNIT]

 
----      ,[NAME]

 
----      ,[ID NUMBER]

 
----      ,[DATE ENGAGED]

 
----      ,[SERVICE NO]

 
----      ,[DESIGNATION]

 
----      ,s.[SUBMISSION_DATE]

 
----  FROM [just_testing].[dbo].[NoDisclosures$] ls

 
----  INNER join [10.10.9.54].bpm.dbo.DOI_CASEDATA s on ls.[ID NUMBER]=s.[EMP_ID_NO]

all i want is to remove the extra submission date.
which basically means i want the latest submission date for each record.



please assist

[rpeare]

You need a summation query (look for the sigma, looks like a capital E, on your toolbar) the default is GROUP BY in the totals line of your query. Change the SUBMISSION DATE field to MAX

[live2ride]

select fileds
from table1
inner join (select id, max(submission_date) as submission_date from table1) as table2
on table1.id = table2.id
and table1.submission_date = table2.submission_date