On Fri, Jun 22, 2012 at 3:28 PM, bwc3068 <avert@...> 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]