javimuu javimuu -3 years ago 96
SQL Question

Select count while count is greater than a specific number in postgres sql

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!

Answer Source
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 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download