A typical record in one of our database tables reads:
<field1>: Joe Bloggs
<field5>: Microsoft Corporation
<field6>: Bill Gates
In this table there will be multiple records for Joe Bloggs each with different data in the rest of the fields; I am trying to bring back one record for each different name in <field1> where the date in <field2> is the maximum date from each set of records matching the name. By using the code attached I can achieve this but if I try to add <field5> and <field6> to the script it shows all records containing Joe Bloggs that match the criteria, not just the one I previously got.
Does any of this make sense? If so can someone please help!
<field3> = xxx
and <field2> <= getdate()
and <field4> in
group by <field1>, <field2>, <field3>
order by <field1>