CODEWITHSUNDEEP
sqlpostgresql
karambaq
karambaq

Reputation: 861

How can I use postgresql count filter with limit without subquery

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?

Upvotes: 1

Views: 1453

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

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:

Upvotes: 1

Related Questions