Fetch not null values first, if not found then fetch null value -Postgres

Question

I have 2 tables. table1 has primary records. table2 is slave table having multiple entries for table1.id. Some of the entries could be blank.

First if table2.data is set AND date is not past, then I have to get that record when I do a join of 2 tables. If there is no record with table2.data having not null value and date is not past, then fetch last updated record from table2, having table2.data IS NULL.

I need a single query to fetch NOT NULL or NULL record.

Table1

id  name   date
1   Abc    2013-12-09
2   Test   2014-12-09
3   Xyz    2012-02-10

Table2

id  user_id  data
1   1        test
2   1        NULL
3   2        NULL
4   3        blah blah.....

If I join 2 tables my result should be like, when I pass user_id = 1 and comparing date as today's date

Result

id   user_id    data
1    1          NULL

If I join 2 tables my result should be like, when I pass user_id = 2

Result

id   user_id    data
1    2          NULL

What queries have you tried to use to get this done so far and what was your logic behind them? — zero298
– zero298, Commented Mar 17, 2014 at 21:21
Your description leaves room for interpretation. And it crumbles at then fetch last updated record from table2: what defines the last update in table2? — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 17, 2014 at 22:42

Erwin Brandstetter · Accepted Answer · 2014-03-18 08:09:56Z

1

This is an educated guess. Your description is too vague.

SELECT DISTINCT ON (t2.user_id)
       *
FROM   table1 t1
JOIN   table2 t2 ON t2.user_id = t1.id
WHERE  t1.date > current_date
ORDER  BY t2.user_id, t2.data DESC NULLS LAST

Assuming:

table2.user_id corresponds to table1.id.
You only want rows where table1.date is a future date.
(user_id, data) is unique in table2, or you need additional sort criteria.

edited Mar 18, 2014 at 8:09

answered Mar 17, 2014 at 22:49

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Fetch not null values first, if not found then fetch null value -Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related