3 Ways To Filter On Order In PostgreSQL
How to filter on order in PostgreSQL and SQLAlchemy
4 min read4 days ago
Not a Medium member? Use the Friend Link to read this article!
Be it to remove duplicates at query time or simply to retrieve the most/least recent (used/ordered/etc) attribute, we implement queries which filter records based on order. Here are 3 implementations in PostgreSQL.
The Data
In a table, fastapi_article , records about my articles are stored, with their content and their text stats (Medium article).
In a materialized view, term_occurrence_per_article_mv, per article and word, the number of times the word appears in the article are stored:
create materialized view term_occurrence_per_article_mv as
select
article_id,
word,
nentry as number_of_occurrences
from fastapi_article,
ts_stat('select article_content_simple_with_no_stop_words from fastapi_article where article_id = ' || '''' ||
fastapi_article.article_id || '''' || '::uuid')Getting The Most Used Word Per Article With Row Number And Filter
With the row_number window function we can calculate the order of the word in an article based on the descending number of…
