Skip to content
Advertisement

I want top ten supplier on basis of balance

I am writing a query using rank function and want top 10 suppliers on the basis of their balance. Below is the query which is i am using.

SELECT VENDOR_NAME, Balance, RANK_MAX
FROM
(SELECT xsl.VENDOR_NAME , nvl(xsl.DR , 0) - nvl(xsl.CR , 0) Balance ,
ROW_NUMBER () OVER (PARTITION BY xsl.VENDOR_NAME ORDER BY xsl.DR  DESC) rank_max
FROM xxng_supp_ledger xsl
)
WHERE RANK_MAX < 10

Advertisement

Answer

Try this query:

SELECT tbl.VENDOR_NAME, tbl.Balance, tbl.RANK_MAX
FROM
(SELECT xsl.VENDOR_NAME , nvl(xsl.DR , 0) - nvl(xsl.CR , 0) Balance ,
RANK() OVER (ORDER BY (nvl(xsl.DR , 0) - nvl(xsl.CR , 0))  DESC) rank_max
FROM xxng_supp_ledger xsl
) tbl
WHERE tbl.RANK_MAX < 11

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement