How to select from different tables, order by date and limit with offset?-CodePudding

The db contains posts and cars tables.

Example data:

car.created_at: 2021-12-01
car.created_at: 2021-11-01
car.created_at: 2021-10-01
post.created_at: 2022-01-01
post.created_at: 2021-12-01

I need to select records from both posts and cars tables.

Also to order them in a descending order by created_at attribute.

And to limit them (thousands of records) with offset (pagination).

The final result should contain records from both tables and start from the newest record, in this case from post.created_at: 2022-01-01.

CodePudding user response：

It isn't clear if this is what you want, but the following query would satisfy what you asked for:

  select created_at from cars
   union 
  select created_at from posts
order by created_at desc 
   limit 1 
  offset 2;

CodePudding user response：

select COALESCE(car.created_at,post.created_at) cr_date
       from car full outer join post on false 
order by cr_date desc
offset 1 limit 3;

results

  cr_date
------------
 2021-12-01
 2021-12-01
 2021-11-01