On Thu, Jun 2, 2016 at 10:40 AM, Felipe Santos <felipe...@gmail.com> wrote:
> > > 2016-06-02 14:23 GMT-03:00 Steve Crawford <scrawf...@pinpointresearch.com> > : > >> Something like: >> >> select max(id) from yourtable where sts=0 and ref_id is null; >> >> That assumes that ref_id is null. It would help to see your table >> structure and the query you tried that doesn't work. If ref_id is actually >> a character string then you might need ref_id='' or coalesce(ref_id,'')='' >> if it can be null or empty string. >> >> Cheers, >> Steve >> >> >> On Thu, Jun 2, 2016 at 10:16 AM, Steve Clark <steve.cl...@netwolves.com> >> wrote: >> >>> Hi List, >>> >>> I am a noob trying to do something that seems like it should be easy but >>> I can't figure it out. >>> >>> I have a table like so: >>> >>> id | ref_id | sts >>> ------------------ >>> 1 | | 0 >>> 2 | 1 | 1 >>> 3 | | 0 >>> 4 | | 0 >>> 5 | 4 | 1 >>> 6 | | 0 >>> 7 | 6 | 1 >>> >>> I want to find the max(id) whose sts is 0 but whose id is not referenced >>> by ref_id. >>> >>> so the answer would be id=3. >>> >>> Thanks for any pointers, >>> Steve >>> >>> -- >>> >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >>> >> >> > > I think sts=0 means ref_id is null > > So, what I think he wants to achieve is: > > select max(id) from yourtable where sts=0 and id not in (select ref_id > from yourtable); > > Isn't it? > > The OP will need to explain further as we are all guessing. As I mentioned in my earlier (accidental top - curses GMail) post, table structures and the query or queries that don't work would be useful. So would a description of the problem that is being solved since there could be better approaches. Cheers, Steve