How can I use postgresql count filter with limit without subquery

Question

I have this query

SELECT 
    count(*) filter(where condition) as cond_cnt
FROM table
LIMIT 100

But count happening on all table, not respecting LIMIT at all

If I do it with a subquery:

SELECT 
    count(*) filter(where condition) as cond_cnt
FROM
(SELECT *
 FROM table
 LIMIT 100
) as sub

Then it works fine. What I am missing?

Remember that in query execution LIMIT is the last thing that runs. Having that in mind, in your first query, 'Limit 100' has no impact as it always returns a single value. But, in the second query, you are first limiting your data set to only 100 rows. — GoonerForLife, Dec 18 '21 at 19:57
Yes, that seems right, but is there still a way to avoid subquery? — karambaq, Dec 18 '21 at 20:01
@karambaq It would help if you explained why you don't want to use a subquery. You already need one that specifies an `order by` for your desired `filter` clause limit to pick rows predictably, or for that `filter` clause to also support an `order by`. — Zegarek, Dec 18 '21 at 23:07
A technical alternative would be a CTE. But your logic *does* require a second query. Even in natural language pseudocode, `from the first 100 records of a table (a query) count those that meet a condition (another query)`. — Stefanov.sm, Dec 18 '21 at 23:40

score 1 · Answer 1 · answered Dec 19 '21 at 01:49

But count happening on all table, not respecting LIMIT at all
What I am missing?

You are missing the well-defined order of events in a SELECT query. LIMIT happens after window-functions are applied. So you need a subquery or CTE to apply the LIMIT first.

See:

Best way to get result count before LIMIT was applied

How can I use postgresql count filter with limit without subquery

1 Answers1