Menu
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.
297
The LIMIT clause can be used with the OFFSET clause to skip a specific number of rows before returning the query for the LIMIT clause. Syntax:SELECT * FROM table LIMIT n OFFSET m; Let's analyze the syntax above. The LIMIT clause returns a subset of “n” rows from the query result.
In PostgreSQL, the RANK() function is used to assign a rank to each row of the query result set within the specified partition. The rank of the first row within each partition is 1. The following illustrates the syntax of the RANK() function: Syntax: RANK() OVER ( [PARTITION BY partition_expression, ... ]
rank and dense_rank are similar to row_number , but when there are ties, they will give the same value to the tied values. rank will keep the ranking, so the numbering may go 1, 2, 2, 4 etc, whereas dense_rank will never give any gaps.
PostgreSQL does not impose a limit on the total size of a database. Databases of 4 terabytes (TB) are reported to exist. A database of this size is more than sufficient for all but the most demanding applications.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
