Max(Date) duplicate dates for a record

[wrightyrx7]

Hi all,

I have the following query to get the most recent record for each person.

SELECT DET_NUMBERA, Max(SUP_START_DTC) AS [Start Date]
FROM CHRISCS_EMSUP
GROUP BY DET_NUMBERA;

However I need it to pull out multiple records if the SUP_START_DTC is the same for the person because people can have more than one.


In addition would it be possible to pull through additional fields from the CHRISCS_EMSUP table? Because this returns the employee number and start date. And for people with multiple records (that have same start date) i will not know which record relates to what.

Thanks in advance
Chris

 

[Ranman256]

its 2 queries, and your query is correct to pull 2 fields: the Max, & the KEY field of the person....
Q1, (which has the needed dates)
now in Q2, use Q1 to pull all matching records from the table...

SELECT * FROM CHRISCS_EMSUP, Q1 inner join [CHRISCS_EMSUP].DET_NUMBERA on Q1.DET_NUMBERA

 

[Brianwarnock]

I think that he will need to include the dats in that join, plus he might like to count the records in each group in Q1 and run Q2 where that is >1

Brian

 

[wrightyrx7]

Thanks Guys,

I included the dates in the join as suggested by Brianwarnock and I have checked a couple manually and it seems to be working

Thanks again.

Brianwarnock what do you mean with the Count, will this help me?

 

[Brianwarnock]

If you count the number of records in each group , with say Count(Sup_STAT_DTC) , in Q1 then in Q2 select only where this is >1 then you will get only the records with duplicates.

Brian