Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Sql Query in oracle sql


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
himabindu_nvn
Asked:
himabindu_nvn
  • 5
  • 3
  • 3
  • +2
1 Solution
 
slightwv (䄆 Netminder) Commented:
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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
use select * from (select county, zip, row_number() over(partition by county order by zip) rank from countyzip) where rank <= 2
0
 
himabindu_nvnAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
virdi_ds,

I don't think that will meet this requirement:  "i need to retrive both the records"
0
 
slightwv (䄆 Netminder) Commented:
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
 
himabindu_nvnAuthor Commented:
CA      9G8 A8T  - in this it has NUMBER|CHAR|NUMBER CHAR|NUMBER|CHAR
CA      W1F 9J3  - CHAR|NUMBER|CHAR NUMBER|CHAR|NUMBER
0
 
sdstuberCommented:
are those the only two formats?  are do you need to check for every possible permutation of character/number?
0
 
awking00Commented:
I don't have database access at the moment, but try the attached.
query.txt
0
 
awking00Commented:
I think it may require a slight modification. Try again.
query.txt
0
 
awking00Commented:

Missing single quote. One more time.
query.txt
0
 
himabindu_nvnAuthor Commented:
I've requested that this question be deleted for the following reason:

found solution
0
 
awking00Commented:
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
 
awking00Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now