Return first available row if initial SELECT finds nothing, in a single statement

Question

I am trying to put together a SQL query that will return data based on a simple WHERE condition but will also return the first available record if the condition is not met.

author_id	post_id	publish_date
1	31	2021-07-23
2	432	2021-06-22
2	555	2020-07-23

Using the data above my basic query would look like this

select *
from posts
where author_id = 1
and publish_date > '2021-07-01'
order by publish_date

Results, ie posts by the author published after a specified date

author_id	post_id	publish_date
1	31	2021-07-23

Since author 2 has no posts after this date I would like it to return the most recent post of the specified author. I want to be able to output "<author> hasn't posted since <search_date>, the most recent post was on <publish_date>" without querying the database multiple times.

Desired result for author_id 2 (who doesn't have any posts where publish_date > '2021-07-01')

author_id	post_id	publish_date
2	432	2021-06-22

Is it possible to query for the first available record if the date condition is not met in a single statement?

Do you need to get those two records as two rows (with the same columns)? — Bergi, Jul 19 '21 at 00:48

score 1 · Answer 1 · answered Jul 19 '21 at 01:38

You can create a function for this and get exactly what you want implementing conditions:

create or replace function some_func(input_author_id integer, input_date date)
    returns setof posts
as
$$
declare
    selected_posts posts%rowtype;
begin
    select * from posts p into selected_posts where p.author_id = input_author_id and p.publish_date > input_date;
    if not found then
        return query
        select * from posts p where p.author_id = input_author_id order by publish_date desc limit 1;
    else 
        return query
        select * from posts p where p.author_id = input_author_id and p.publish_date > input_date;
    end if;
end
$$
language plpgsql;

After that, just pass the values of the parameters you want:

select * from some_func(2,'2021-07-01');

And this will give you the below result:

author_id	post_id	publish_date
2	432	2021-06-22

Erwin Brandstetter · Accepted Answer · 2021-07-19T03:03:30.923

Possible with a single query:

WITH cte AS (
   SELECT *
   FROM   posts
   WHERE  author_id = 1
   AND    publish_date > '2021-07-01'
   ORDER  BY publish_date
   )
TABLE cte
UNION ALL
(  -- parentheses required
SELECT *
FROM   posts
WHERE  NOT EXISTS (SELECT FROM cte)
AND    author_id = 1
AND    publish_date <= '2021-07-01'
ORDER  BY publish_date DESC NULLS LAST
LIMIT  1
);

Combining 3 SELECT statements to output 1 table

Or with a PL/pgSQL function:

CREATE OR REPLACE FUNCTION my_func(_author_id int, _publish_date date)
  RETURNS SETOF posts
  LANGUAGE plpgsql aS
$$
BEGIN
   RETURN QUERY
   SELECT *
   FROM   posts
   WHERE  author_id = _author_id
   AND    publish_date > _publish_date
   ORDER  BY publish_date;
   
   IF NOT FOUND THEN
      RETURN QUERY
      SELECT *
      FROM   posts
      WHERE  author_id = _author_id
      AND    publish_date <= _publish_date
      ORDER  BY publish_date DESC NULLS LAST
      LIMIT  1;
   END IF;
END
$func$;

Call:

SELECT * FROM my_func(2,'2021-07-01');

Return first available row if initial SELECT finds nothing, in a single statement

2 Answers2