Hello,
I am trying to pull some registration data out, but due to our applications shortcomings, the query below pulls multiple registrations for the same ID. This is because the "Extra Field" tables don't update a record when changed, it simply writes a new one. Very annoying and we are trying to work fix this, but right now I need to work around it. So here is the query:
Code:
SELECT DISTINCT
sx_Registration.RegID, sx_Registration.LastName, sx_Registration.FirstName, sx_Registration.CompanyName, sx_Registration.EmailAddress,
sx_RegistrationExtra.ExtraField1, sx_RegistrationExtra.ExtraField2, sx_RegistrationExtra.ExtraField3
FROM sx_Registration
INNER JOIN
sx_EventStat ON sx_EventStat.RegID = sx_Registration.RegID AND sx_Registration.RegID = sx_EventStat.RegID
FULL OUTER JOIN
sx_RegistrationExtra ON sx_Registration.RegID = sx_RegistrationExtra.RegID AND sx_RegistrationExtra.RegID = sx_EventStat.RegID
WHERE (sx_EventStat.EventID = 62)
This is supposed pull the registration data that is attached to a particular event ID. When that query is run, repeats show up that have the same Registration ID attached to it. I simply want to grab the last one in the list it pulls up.
An example of what shows up within a given ID (which is the first column) when the above query is run:
Code:
8445 abc a abc abc@abc.com abc No company website
8445 abc a abc abc@abc.com abc No web
8445 abc a abc abc@abc.com Corporate Client No WRB website
8445 abc a abc abc@abc.com Institutional Client No abc
8445 abc a abc abc@abc.com Institutional Client No colleague
8445 abc a abc abc@abc.com Institutional Client No friend
8445 abc a abc abc@abc.com Institutional Client No google
8445 abc a abc abc@abc.com Institutional Client No news
8445 abc a abc abc@abc.com Institutional Client No web
8445 abc a abc abc@abc.com Institutional Client No word
8445 abc a abc abc@abc.com investor No friend
8445 abc a abc abc@abc.com Retail Client No press
I just want the last line as that is the most recent edit of that particular field. Functions like MAX and LAST don't work as it has to repeat for each ID.
Thank you for you help in this matter.