oracle-databaseanalytic-functions

SQL Oracle - Filter on Rank/Window Partition

I have a table of receiving events by item and branch (skul) and I'm trying to pull only the most recent receipt date for each skul. I'm having trouble with an approach for the where clause.

Any help would be appreciated, here is SQL I am using now.

SELECT 
branch||item "skul",
date,
order_num,
RANK () OVER ( 
            PARTITION BY branch||item
            ORDER BY date DESC
        ) "Rank"

FROM    receipts
Solution

  • Rather than partitioning on the concatenation of the branch and item, you should just partition on both those columns:

    WITH cte AS (
    SELECT branch, item, "date", order_num,
           RANK() OVER (PARTITION BY branch, item ORDER BY "date" DESC) rnk
    FROM yourTable
)

SELECT branch, item, "date", order_num
FROM cte
WHERE rnk = 1;