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