postgresql - Select count while count is greater than a specific number in postgres sql -
i want last month records table. have tried:
select count(*) numberofrows table created_at > current_date - interval '1 months'
it's ok, want add conditions:
if numberofrows >= 10, nothing (numberofrows can 20, 30, ...)
else if numberofrows < 10, select table until numberofrows = 10 (last 2 months, 3 months, etc...).
how can that? in advances!
with curr_month_cnt ( select count(*) cnt your_table created_at > current_date - interval '1 months' ) select * your_table created_at > current_date - interval '1 months' union select t.* ( select * your_table created_at <= current_date - interval '1 months' , (select cnt curr_month_cnt) < 10 order created_at desc limit greatest(0, 10 - (select cnt curr_month_cnt)) ) t
this return maximum of 10 records, starting recent month , going backwards. in event latest month have not 10 records, 2 , 3 months old data returned, in order.
Comments
Post a Comment