Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sql Query in oracle sql

Posted on 2011-09-29
15
Medium Priority
?
371 Views
Last Modified: 2013-11-11

I have a table with two fields Country and Zip. say like
country-zip
US      60074
US      99001-9900
us      99000
IN      600741
IN      834938
CA      1A2 C3G
CA      9K0 I9Y
PK      999
GB      9999999


I want the result to be something like this :
country-zip
US      99001-9900
us      99000
IN      600741
CA      1A2 C3G
PK      999
GB      9999999

It can choose any zip for one country if there are two.
Please help

I need to retrive the data based on the zipcode format like if for 'US'
there the 2 different formats like 60074 and 60074-9430 In this case i need to retrive both the records.

Database Version : Oracle 11.X
0
Comment
Question by:himabindu_nvn
[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
  • 5
  • 3
  • 3
  • +2
15 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36816046
I don't understand the piece about BOTH records if the formats exist.  Do you mean if the first 5 are the same and one is zip+4 retuirn both but if the fisrt 5 don't match, pick one?
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36816050
use select * from (select county, zip, row_number() over(partition by county order by zip) rank from countyzip) where rank <= 2
0
 

Author Comment

by:himabindu_nvn
ID: 36816129
There are multiple zipcode formats for one country then i need to retrive atleast one record for each zipcode format.
Like.
US      99999
US      99999-9999
US      93838-8943
US      83293
CA      9G8 A8T
CA      8F9 A9A
CA      W1F 9J3
PL      190-9292
PL      919-9929
IN      922299
IN      923092

Output:
US      99999
US      93838-8943
CA      9G8 A8T
CA      W1F 9J3
PL      190-9292
IN      922299

Need to select atleast once record for each number format..
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36816135
virdi_ds,

I don't think that will meet this requirement:  "i need to retrive both the records"
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36816152
I don't see how you pick:
CA      9G8 A8T
CA      W1F 9J3


from:
CA      9G8 A8T
CA      8F9 A9A
CA      W1F 9J3


What defines 'different formats' here?
0
 

Author Comment

by:himabindu_nvn
ID: 36816200
CA      9G8 A8T  - in this it has NUMBER|CHAR|NUMBER CHAR|NUMBER|CHAR
CA      W1F 9J3  - CHAR|NUMBER|CHAR NUMBER|CHAR|NUMBER
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36816407
are those the only two formats?  are do you need to check for every possible permutation of character/number?
0
 
LVL 32

Expert Comment

by:awking00
ID: 36905060
I don't have database access at the moment, but try the attached.
query.txt
0
 
LVL 32

Expert Comment

by:awking00
ID: 36905073
I think it may require a slight modification. Try again.
query.txt
0
 
LVL 32

Expert Comment

by:awking00
ID: 36917143

Missing single quote. One more time.
query.txt
0
 

Author Comment

by:himabindu_nvn
ID: 36988418
I've requested that this question be deleted for the following reason:

found solution
0
 
LVL 32

Expert Comment

by:awking00
ID: 36988420
I think it's only fair that when a questioner requests a delete because he or she found a solution, that the solution should be posted before the delete is allowed.
0
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 36993051
Attached is the proof that my offered solution works for the two sets of example data provided in this thread. If the questioner found a better method, I would like to see it.
validation.txt
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…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

719 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