Re: dumb question

On Thu, Jun 2, 2016 at 10:40 AM, Felipe Santos <felipepts(at)gmail(dot)com> wrote:

>
>
> 2016-06-02 14:23 GMT-03:00 Steve Crawford <scrawford(at)pinpointresearch(dot)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(dot)clark(at)netwolves(dot)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(at)postgresql(dot)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