Member Avatar for wfbp

I am attempting to pull a single record with the most recent affective date. However, I am getting two records with effective dates of 04/01/2010 and 12/01/2009. Can someone please tell me how to pull only the record with an effective date of 04/01/2010? Thanks for your help.

select 
T1.idNumber, T1.lastname, T1.firstname, T1.streetaddress, 
T1.city, T1.state, T1.zipcode, T1.DOB, T1.DOD, 
T2.mNumber, countycode, max(T2.effectiveDate) 
from 
PTable T1, 
MTable T2 
where 
T1.idNumber in ('12345') 
group by 
T1.idNumber, T1.lastname, T1.firstname, T1.streetaddress, 
T1.city, T1.state, T1.zipcode, T1.DOB, T1.DOD, 
T2.mNumber, T1.countycode
  • 2 Contributors
  • 1 Reply
  • 135 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by jbisono
Member Avatar for jbisono

First that is happening because you are not linking your two tables with a particular field, if that is what you want I would say SELECT TOP 1 THE REST OF THE CODE HERE.

But this is what I think you need and the FROM Clause Remove everything and add this
PTable AS T1 INNER JOIN
MTable AS T2 ON T1.idNumber = T2.mNumber

I'm assuming that idNumber and mNumber is the same ID. that should work.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.