Re: dumb question

[Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx>]

On Thu, Jun 2, 2016 at 10:40 AM, Felipe Santos <felipepts@xxxxxxxxx> wrote:

2016-06-02 14:23 GMT-03:00 Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx>:

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.clark@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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