|
|
|
Junior Member
 
Group: Forum Members
Last Login: 10/10/2008 3:18:48 AM
Posts: 24,
Visits: 112
|
|
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
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 8/15/2008 11:52:29 AM
Posts: 9,
Visits: 17
|
|
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;
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 10/10/2008 3:18:48 AM
Posts: 24,
Visits: 112
|
|
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.
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 8/15/2008 11:52:29 AM
Posts: 9,
Visits: 17
|
|
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?
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 10/10/2008 3:18:48 AM
Posts: 24,
Visits: 112
|
|
Thanks Dales, it works.
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|