Does any one knows how to create a query that will pick the highest priority of from the list below, if I have an ID_Number and Passport, the query must pick ID_Number since is the one with the highest priority. Identity Type ID_Type_Cd Priority ID_Number 18 1 Passport 15 2 Other 0 3
I have table A which will be linked to table B containing my pririoty list, Table A is as follows: SELECT party_id ,O_Party_Id ,O_Party_Role_Cd ,Ext_Identification_Num ,Ext_Identification_Type_Cd FROM VW_Party_Identification A
and Table B Select Ext_Identification_Type_Cd ,Priority ,Party_Type_Cd From Priority B; So how do I join the two queries to make sure that I bring back the highest priority for Party_Id incase party_id has more than one Ext_Identification_Type_Cd.
The two tables join on Ext_Identification_Type_Cd.
Select * from VW_Party_Identification where ext_identification_type_cd in (Select Ext_Identification_Type_Cd from (select ext_identification_type_cd, rank() over (order by priority asc) rowrank from Priority) subtable1 where rowrank=1) )