Thread: dumb question

dumb question

From

Steve Clark

Date:

02 June 2016, 23:01:39

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

--

Re: dumb question

From

Dann Corbit

Date:

02 June 2016, 23:08:09

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

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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.

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

Re: dumb question

From

Dann Corbit

Date:

02 June 2016, 23:11:55

If ref_id is an instance of id and you are trying to filter that out, then use a self join

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dann Corbit
Sent: Thursday, June 2, 2016 1:08 PM
To: 'Steve Clark' <steve.clark@netwolves.com>; pgsql <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] dumb question

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

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: dumb question

From

Steve Clark

Date:

02 June 2016, 23:13:53

On 06/02/2016 04:07 PM, Dann Corbit 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
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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.
>
> so the answer would be id=3.
>
> Thanks for any pointers,
> Steve
>
Hi Dan,

Thanks for the response - but I think that would give me id=6 and not id=3.

--
Stephen Clark

Re: dumb question

From

"David G. Johnston"

Date:

02 June 2016, 23:16:18

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@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 <DCorbit@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.