CODEWITHSUNDEEP
postgresql
tandy
tandy

Reputation: 1948

Postgres RANK() with ts_rank() returning same rank for all rows

I'm trying to return only the top N results for a query that utilizes ts_rank(). I do not know what N will be for each query, so I can not ORDER BY score DESC LIMIT X, because I don't know what X will be.

In my current approach, I use rank() in conjunction with ts_ranking and the result is that the ranking is always the same. How do I get the ranks to be different, based on the score returned by the ts_ranking? There should be two ranking groups of three names here: 1 and 2 (based on the score shown).

SELECT rank_filter.* FROM (
    SELECT 
        name, 
        ts_rank(textsearchable_index_col, keywords) as score,
        rank() OVER (
            PARTITION BY ts_rank(textsearchable_index_col, keywords)
        ) as ranking
    FROM organization, to_tsquery('english', 'Obviously') keywords
    WHERE keywords @@ textsearchable_index_col ORDER BY score DESC
) as rank_filter;

          name          |   score   | ranking
------------------------+-----------+---------
 B-Obvious              | 0.0759909 |       1
 Obviously              | 0.0759909 |       1
 Obvious Engineering    | 0.0759909 |       1
 LexDex                 | 0.0607927 |       1
 Qualitest Turkey       | 0.0607927 |       1
 Obvious Software       | 0.0607927 |       1

The DESIRED output would be this:

         name          |   score   | ranking
-----------------------+-----------+---------
 B-Obvious              | 0.0759909 |       1
 Obviously              | 0.0759909 |       1
 Obvious Engineering    | 0.0759909 |       1
 LexDex                 | 0.0607927 |       2
 Qualitest Turkey       | 0.0607927 |       2
 Obvious Software       | 0.0607927 |       2

Upvotes: 1

Views: 1642

Answers (1)

BShaps
BShaps

Reputation: 1414

You're going to want to both replace the PARTITION BY with ORDER BY and also use dense_rank instead of rank. The following should work for your desired results:

SELECT rank_filter.* FROM (
    SELECT 
        name, 
        ts_rank(textsearchable_index_col, keywords) as score,
        dense_rank() OVER (
            ORDER BY ts_rank(textsearchable_index_col, keywords) DESC
        ) as ranking
    FROM organization, to_tsquery('english', 'Obviously') keywords
    WHERE keywords @@ textsearchable_index_col ORDER BY score DESC
) as rank_filter;

Upvotes: 3

Related Questions