CODEWITHSUNDEEP
postgresqlgreatest-n-per-grouppostgresql-9.3
user6508070
user6508070

Reputation:

How to retrieve top 3 results for each column in postgresql?

I have given a question.
The table looks like this..

STATE          | year1 | ... | year 10
AP             | 100   | ... | 120
assam          | 13    | ..  | 42
madhya pradesh | 214   | ... | 421

Now, I need to get the top - 3 states for each year.

I tried everything possible. But, I am not able to filter results per column.

Upvotes: 0

Views: 64

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You have a design problem. The enumerated column are almost always a sign of bad design.

For now you could unpivot using unnest and then use window function row_number to get the top 3 states per year:

with unpivoted as (
    select state,
        unnest(array[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) as year,
        unnest(array[
            year_1, year_2, year_3, 
            year_4, year_5, year_6, 
            year_7, year_8, year_9, 
            year_10
            ]) as value,
    from your_table
    )
select *
from (
    select t.*,
        row_number() over (
            partition by year 
            order by value desc
            ) as seqnum
    from unpivoted t
    ) t 
where seqnum <= 3;

Demo

Upvotes: 1

Related Questions