hi all--

(always a great place to get answers!!)

here is the sql I'd prefer to use (a join)

select distinct t.plantkey, t.mostrecent, t.tagnumber, t.datetested, t.equipmentkey, o.equipmentkey, o.datetested from reliefd t
join reliefd o
on t.equipmentkey=o.equipmentkey
where
t.mostrecent='T' and
t.PlantKey='20030319103909097704' and
o.datetested<='2005'
order by
t.tagnumber

the problem is, the distinct is for the entire returned records (which are all unique). it returns 224 records

PLANTKEY MOSTRECENT TAGNUMBER DATETESTED EQUIPMENTKEY EQUIPMENTKEY DATETESTED

20030319103909097704 T PSV-100C 2010/02/21 20031219131654053330 20031219131654053330 2003/12/04
20030319103909097704 T PSV-100C 2010/02/21 20031219131654053330 20031219131654053330 2004/12/01


what i really want is

distinct on the field t.equipmentkey

i can get the proper result (124 records) with the nested select shown below

select t.plantkey, t.mostrecent, t.tagnumber, t.datetested, t.equipmentkey from reliefd t
where
t.mostrecent='T' and
t.PlantKey='20030319103909097704' and
t.equipmentkey in ( select equipmentkey from reliefd where datetested<='2005')
order by
t.tagnumber


PLANTKEY MOSTRECENT TAGNUMBER DATETESTED EQUIPMENTKEY

20030319103909097704 T PSV-100C 2010/02/21 20031219131654053330

but i don't really want to use the nested selects

is there something else i can do with the join?

thanks
kelly