PostgreSQL include rows which contains a column null in the results

Question

I created the following snippet to let you know the issue I have

The query is used to get all the rows where I have the most recent day, study_id and status of a candidate from that table.

As you see from the example I'm getting only 2 rows but I should have more also including the rows having site_id === null.

I should have included also the row

('2020-01-02 00:00:00+00', 'TBX1', NULL, 'INCOMPLETE', 120, 100)

The final result I'm expecting

study_id	site_id	status	day	day	study_id	site_id	status	total	current
TBX1	SWE-1	INCOMPLETE	2020-01-02 00:00:00+00	2020-01-02 00:00:00+00	TBX1	SWE-1	INCOMPLETE	37	31
TBX1	SWE-2	INCOMPLETE	2020-01-02 00:00:00+00	2020-01-02 00:00:00+00	TBX1	SWE-2	INCOMPLETE	43	41
TBX1	null	INCOMPLETE	2020-01-02 00:00:00+00	2020-01-02 00:00:00+00	TBX1	null	INCOMPLETE	120	100

So, therefore, is included the row also wth site_id null

Kadet · Accepted Answer · 2022-06-21 14:21:30Z

1

Is it "take last from each group" problem ?

select distinct on (study_id, site_id,status) 
  candidates.* from candidates
WHERE
        "study_id" in('TBX1')
        AND "status" in('INCOMPLETE')
order by study_id, site_id, status , day desc

DB Fiddle

answered Jun 21, 2022 at 14:21

Kadet

1,4195 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Cetin Basoz Over a year ago

Why I couldn't think of using postgreSQL's power on this! Nice and I would mark this as the answer @Jakub.

Jakub Over a year ago

I marked and very nice solution however I need to use count on the current column and I'm not sure in this last solution with distinct how would that work but in general is amazing yes

Kadet Over a year ago

@Jakub, tell, what you want to count. You can always use subquery for counting or count() over (), but it depends on range of elements you want to count.

Jakub Over a year ago

Yes I need to count the tola of the current column the sum of those numbers

Kadet Over a year ago

In this case I propose dbfiddle.uk/…

|

Cetin Basoz · Accepted Answer · 2022-06-21 13:57:29Z

You can't directly compare NULL. NULL = NULL check using = operator doesn't yield TRUE or FALSE but NULL.

Instead you can check if they are both NULL. ie:

SELECT
    --sum("current")
    *
FROM (
    SELECT
        "study_id",
        "site_id",
        "status",
        max("day") AS "day"
    FROM
        "candidates"
    WHERE
        "study_id" in('TBX1')
        AND "status" in('INCOMPLETE')
    GROUP BY
        "study_id",
        "site_id",
        "status") AS "latest"
    INNER JOIN "candidates" ON "latest"."day" = "candidates"."day"
        AND "latest"."study_id" = "candidates"."study_id"
        AND ("latest"."site_id" = "candidates"."site_id" or 
            ("latest"."site_id" is null and "candidates"."site_id" is null))
        AND "latest"."status" = "candidates"."status";

DBFiddle demo

Collectives™ on Stack Overflow

PostgreSQL include rows which contains a column null in the results

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related