> -----Original Message----- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Steve Clark > Sent: Thursday, June 2, 2016 9:56 AM > To: pgsql <pgsql-general@postgresql.org> > Subject: [GENERAL] dumb question > > 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. >
On Thu, Jun 2, 2016 at 4:07 PM, Dann Corbit <dcor...@connx.com> wrote: > This is your request, translated directly into SQL > > select max(id) from sometable where sts=0 and ref_id IS NULL > > Looking at your sample, it seems that sts is always 1 when ref_id exists, > so it may possibly simplify to: > > select max(id) from sometable where sts=0 > > Please don't top-post. Your query would select "id=6", which is disqualified due to id=7... For the record one reads: "whose id is not referenced by ref_id" AS "id NOT IN (ref_ids....)"; ref_id IS NULL means "that lacks a ref_id" and is evaluated independent of the id. David J.
