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
himabindu_nvnAsked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
All Courses

From novice to tech pro — start learning today.