I want to get last month records from table. I have tried:
SELECT count(*) as numberOfRows from Table where created_at > CURRENT_DATE - INTERVAL '1 months'
It's Ok, but I want to add some conditions:
If numberOfRows >= 10, do nothing (numberOfRows can be 20, 30, ...)
else if numberOfRows < 10, select from this table until numberOfRows = 10 (last 2 months, 3 months, etc...).
How can I do that? Thanks in advances!
WITH curr_month_cnt AS (
SELECT COUNT(*) AS cnt
FROM your_table
WHERE created_at > CURRENT_DATE - INTERVAL '1 months'
)
SELECT *
FROM your_table
WHERE created_at > CURRENT_DATE - INTERVAL '1 months'
UNION ALL
SELECT t.*
FROM
(
SELECT *
FROM your_table
WHERE
created_at <= CURRENT_DATE - INTERVAL '1 months' AND
(SELECT cnt FROM curr_month_cnt) < 10
ORDER BY created_at desc
LIMIT
GREATEST(0, 10 - (SELECT cnt FROM curr_month_cnt))
) t
This will return a maximum of 10 records, starting with the most recent month and going backwards. In the event that the latest month have not 10 records, then two and three months old data would be returned, in that order.
GREATEST() to limit the expression to being no smaller than zero. This should get around this problem.Based on your description, you would seem to want:
select greatest(10, count(*)) as numberOfRows
from Table
where created_at > CURRENT_DATE - INTERVAL '1 months';
This seems rather surprising. Perhaps you want:
select (case when sum( (CURRENT_DATE - INTERVAL '1 months' ) :: int) >= 10
then sum( (CURRENT_DATE - INTERVAL '1 months' ) :: int)
else least(10, count(*))
end) as numberOfRows
from Table
where created_at > CURRENT_DATE - INTERVAL '1 months';
greatest(count(*), 10)?