Get last two rows from a row_number() window function in snowflake-CodePudding

Hopefully, someone can help me...

I'm trying to get the last two values from a row_number() window function. Let's say my results contain row numbers up to 6, for example. How would it be possible to get the rows where the row number is 5 and 6?

Let me know if it can be done with another window function or in another way.

Kind regards,

CodePudding user response：

Using QUALIFY:

SELECT *
FROM tab
QUALIFY ROW_NUMBER() OVER(ORDER BY ... DESC) <= 2;

This approach could be further extended to get two rows per each partition:

SELECT *
FROM tab
QUALIFY ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ... DESC) <= 2;

CodePudding user response：

You can use top with order by desc like:

select top 2 row_number() over([partition by] [order by]) as rn
from table
order by rn desc