Re: dumb question

2016-06-02 14:23 GMT-03:00 Steve Crawford <scrawford@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.clark@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?

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.   

On Thu, Jun 2, 2016 at 3:23 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce <pierce@hogranch.com> wrote:
>> Thanks all the below seem to do the trick.

I doubt it -- using NOT IN requires (per the SQL specification)
handling NULLs in a way that probably does not give you the answer
you want.  (NOT IN also is often much slower than the NOT EXISTS
test which will actually give you the answer you want.)

test=# create table t (id int not null primary key, ref_id int, sts
int not null default 0);
CREATE TABLE
test=# insert into t values
(1,null,0),(2,1,1),(3,null,0),(4,null,0),(5,4,1),(6,null,0),(7,6,1);
INSERT 0 7
test=# select max(id) from t where sts=0 and id not in (select ref_id from t);
 max
-----

(1 row)

test=# select max(id) from t t1 where sts = 0 and not exists (select *
from t t2 where t2.ref_id = t1.id);
 max
-----
   3
(1 row)

Note that providing minimal setup (like the above) helps in getting
good answers quickly.

>> do note, this is whats known as an 'anti-join', and these can be pretty
>> expensive on large tables.
>
> +1

*Can* be.  Proper indexing can make them very reasonable.

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

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.

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@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