Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

> Can I filter based on the results of a window function? (no! window functions happen in SELECT, which happens after both WHERE and GROUP BY)

Yes, you can by wrapping the entire SELECT in another SELECT (or use WITH)

    select *
    from (select ...) as x
    where ...
This works because the result of a SELECT is a (virtual) table on its own. It was an eye opener when I saw it for the first time.



It's surprising to me that someone can run into window functions earlier than subqueries. I guess my background with MySQL which only recently supported window functions affected my perspective there.


Her question that starts the post is about doing it in a single query (no subqueries.) I kinda had this misunderstanding on Twitter too when responding to an earlier diagram of hers https://twitter.com/firasd/status/1172874054002307073


I've had use cases for subqueries long before I knew about window functions. This was just the example from the article where subqueries provide a solution.




Consider applying for YC's Fall 2025 batch! Applications are open till Aug 4

Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: