?
Solved

Need help with formulating a query

Posted on 2011-02-18
1
Medium Priority
?
248 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
1 Comment
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

621 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