CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-groupwindow-functions
Edmon
Edmon

Reputation: 4872

Using postgres rank function to limit to n top results

I am querying an account payables table that has list of ap documents of which each has ( among other fields) the ones I am interested in to run aggregate queries over:

vendor_id, amount and a date.

I would like to construct the query over this table to where I would be getting, grouped by year, top 10 vendors ordered by total (sum of amounts).

Would someone tell me please how to use rank function for this.

Upvotes: 7

Views: 11359

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

select *
from (
    select the_year, vendor_id, amount,
        row_number() over(
            partition by the_year
            order by amount desc
        ) as rn
    from (
        select
            date_trunc('year', the_date) as the_year,
            vendor_id,
            sum(amount) as amount
        from ap
        group by 1, 2
    ) s
) s
where rn <= 10
order by the_year, amount desc

Upvotes: 8

Related Questions