CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformwindow-functions
JVA
JVA

Reputation: 131

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

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,

Upvotes: 0

Views: 1953

Answers (3)

Bertek
Bertek

Reputation: 9

I'd say @Shmiel is the formal and elegant way, just in case, would be the same as :

WITH CTE AS
         (SELECT product,
                 user_id,
                 ROW_NUMBER() OVER (PARTITION BY user_id order by product desc)
                     as RN
          FROM Mytable)
SELECT product, user_id
FROM CTE
WHERE RN < 3;

You will use order by [order_condition] with "desc". And then you will use RN(row number) to select as many rows as you want

Upvotes: 1

Shmiel
Shmiel

Reputation: 1253

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

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;

Upvotes: 3

Related Questions