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
– GoonerForLife, Commented Dec 18, 2021 at 19:57
Yes, that seems right, but is there still a way to avoid subquery? — karambaq
– karambaq, Commented Dec 18, 2021 at 20:01
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
– Stefanov.sm, Commented Dec 18, 2021 at 23:40

Erwin Brandstetter · Accepted Answer · 2021-12-19 01:49:48Z

1

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

answered Dec 19, 2021 at 1:49

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I use postgresql count filter with limit without subquery

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related