Solved

Need help with formulating a query

Posted on 2011-02-18
1
221 Views
Last Modified: 2012-05-11
I have two tables - address and code_map

address table contains - id, seq_nbr,addr_type, street, state, country,natn_cd
code_map contains natn_cd, nation_desc

The valid addr_types are PR,MA,LO. Whenever a new PR address type is added for an id, the seq_nbr gets incremenented

Ex.

id                 seq_nbr            addr_type         street    state      natn_cd
1234               1                      PR                     abc      MD         0001
1234               2                      PR                     xyz      MD         0001
1234               1                      MA                    qwe    MD          0001

And then code_map contains 0001       USA

I need to write a query which will fetch me an address with the following priority - first look for PR, then MA, then LO. And within each addr_type the row with the maximum seq_nbr should be returned. Also, the fields to be returned should include nation_desc so a join with code_map should be performed.

Thanks
0
Comment
Question by:happylife1234
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 34927113
Something like (typed in, untested):

select nation_desc
from code_map c,
(select natn_cd, row_number() over(partition by id order by decode(addr_type,'PR',1,'MA',2,'LO',3,999) asc, seq_nbr desc) myrownum
) x
where x.myrownum=1 and x.natn_cd=c.natn_cd;
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question