On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce <pie...@hogranch.com> wrote:
> On 6/2/2016 11:10 AM, Steve Clark wrote: > > Thanks all the below seem to do the trick. > > On 06/02/2016 01:58 PM, David G. Johnston wrote: > > select max(id) from yourtable where sts=0 and id not in (select ref_id > from yourtable); > > > select max(id) from yourtable where sts=0 and id not in (select ref_id > from yourtable); > > > do note, this is whats known as an 'anti-join', and these can be pretty > expensive on large tables. > +1 Though I suspect that with a partial index on (id, sts=0) and (ref_id, ref_id IS NOT NULL), though highly sensitive to density, that even for large total row counts it would perform pretty well; but I'm not knowledgeable in how smart we are here. Selecting, in descending order, (id where sts = 0), from the index and then poking into index(ref_id) should, particularly if the cross-set is sparse, pretty quickly find a non-match. David J.
