snowflake cloud data platform - Get top 3 IDs with highest amount using window calculation in matillion etl

Question

I have a table that looks like this:

id | name | amount 1 jesse 192$ 2 sam 123$ 3 steve 345$ 4 josh 234$ 5 hamish 452$ 6 john 211$ 7 watson 111$ 8 amir 143$ 9 riaz 311$ 10 panag 139$ Now, I want the id's of the top 3 people with the highest amount. I searched some around like window functions,aggregate component but I couldn't really find what I need. Can anyone help me with this?

i want the result to look like

id amount 5 452$ 3 345$ 9 311$

Answer 1

You need to use the rank component, order by amount, window function = row number. Then feed that into a filter component, and select row number <=3