On Fri, Jun 22, 2012 at 3:28 PM, bwc3068 <[email protected]> wrote:
> 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 > > > 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 > You could try grouping the join on t.plantkey, then selecting max() of each of the other fields. Good luck, Ann > > > 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 > > > > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links > > > > [Non-text portions of this message have been removed]
