Rank in Oracle

 

DENSE_RANK() OVER (PARTITION BY r.reward_number ORDER BY ar.account_type_id desc)

 

here 1 reward number got 3 account type and we want to display only the first account type.

Example:

SELECT



*

FROM

(

SELECT

r.reward_number "Reward Id",

r.description "Description",

web_display_name "Web Description",

TO_CHAR (valid_from, 'dd/mm/yyyy') "Valid From",

TO_CHAR (valid_to, 'dd/mm/yyyy') "Valid To",

CASE

WHEN wallet_id IS NULL THEN 'National'

ELSE (SELECT w.DESCRIPTION FROM wallet w WHERE w.wallet_id= r.wallet_id)

END

"Wallet",

at.DESCRIPTION "Account Type",

goods_total_points_cost "Points Cost",

PRODUCT_DISCOUNT_PERCENT "Product Discount",

FIXED_PRODUCT_PRICE "Fixed Product Price",

FIXED_DISCOUNT_AMOUNT "Fixed Product Discount",

MAX_TRIGGER_QUANTITY "Max Trigger Quantity",

TENDER_OR_DISCOUNT_FLAG "Tender Or Discount Flag",

CASE WHEN TENDER_OR_DISCOUNT_FLAG IS NOT NULL THEN MAX_QUANTITY

ELSE 0 END "Max Quantity",

NVL(r.MINIMUM_SPEND,0) "Minimum Spend", --#17900

DENSE_RANK() OVER (PARTITION BY r.reward_number ORDER BY ar.account_type_id desc) rk -- 18394

FROM REWARD r inner join ACCOUNT_REWARD ar on r.reward_id=ar.reward_id

inner join ACCOUNT_TYPE at on ar.account_type_id= at.account_type_id

WHERE

external_id_export =i_voucher_number

AND r.deleted_flag = 'N' and at.deleted_flag='N'

--18394 start

AND r.auto_print_flag = 'N'

AND TRUNC(SYSDATE) BETWEEN valid_from AND valid_to

--18394 end

ORDER BY r.description

) r WHERE rk=1; --18394