thanks for the replies which is faster, a "subselect" or "exists"?
or does it matter? i've shy'ed away from sub-selects because of speed issues (fears)? thanks again kelly --- In [email protected], Svein Erling Tysvær <svein.erling.tysvaer@...> wrote: > > Oops, didn't notice it was a self join. > > What I now think you want, is > > select t.plantkey, t.mostrecent, t.tagnumber, t.datetested, t.equipmentkey > from reliefd t > where t.mostrecent='T' and > t.PlantKey='20030319103909097704' and > exists(select * from reliefd o where t.equipmentkey=o.equipmentkey and > o.datetested<='2005') > order by t.tagnumber > > I guess you don't like subselects (although I don't understand why), using a > CTE would get you the same result: > > WITH tmp(equipmentkey) > AS > (SELECT DISTINCT equipmentkey FROM reliefd > WHERE datetested <= '2005') > SELECT t.plantkey, t.mostrecent, t.tagnumber, t.datetested, t.equipmentkey > FROM reliefd t > JOIN tmp o ON t.equipmentkey = o.equipmentkey > WHERE t.mostrecent='T' AND > t.PlantKey='20030319103909097704' > > HTH, > Set >
