Highest Priority

Database
Enthusiast

Highest Priority

Hi

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
4 REPLIES
Enthusiast

Re: Highest Priority

If the table is named LT_IDENTITY, try:

select identity_type, identity_type_cd, priority from
(select identity_type, identity_type_cd, priority,
rank() over (order by priority asc) rowrank
from LT_IDENTITY) a
where rowrank=1;
Enthusiast

Re: Highest Priority

Hi

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.
Enthusiast

Re: Highest Priority

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)
)

How about that?
Enthusiast

Re: Highest Priority

Thanks Dales, it works.