3 Tables to join, repeats to remove

[Gazthrak]

I will get to the SQL first (I should point out I am working in Microsoft SQL):


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)


What I am trying to do is 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):

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. This should repeat for each ID.

Thank you for you help in this matter.

[crash41301]

Is there no date or timestamp in the data? If so, use a correlated subselect to get only the newest record for each user.

Basically, you need a way to identify which record is the newest of the bunch if thats all you want to grab. I'm not seeing how you would know this in your raw data example? Are you relying soley on what record position the data shows up as? If so, this is not a good way to approach storing data and retrieving only the "newest" of the logical data. For example, a simple table-reorg would muck your ordering up with ease.