How can I select a primary and secondary row from the same table in Oracle?

by Dennis Konkler
(South Dakota)


In my Oracle database I have a table with county codes that has columns of county code and description. Each county has 2 rows. One has county_code as 2 letters and 3 numbers (ie. "AA123") and county_desc as 2 letter state code, a hyphen, and the county name (ie. "AA-Myctyname"). The other is county_code as alpha (ie. "AAMCN") and county_desc (ie. "Myctyname, AA").


I am given the alphanumeric code ("AA123") but I must display the all alpha code ("AAMCN").

What is a simple SELECT statement to use to read the correct all alpha code or what is the easiest way to provide the information required?

Thanks!

If we've understood the question correctly, you have 2 codes for each county, you are given the value of one and want to find the other.

The way to do this is in Oracle is to use a subquery - the county code you are given will be your entry point in to the table and will give you the county description. Assuming the county name part of the county_desc field is the same (and that abbreviations are either not used or are used consistently) you can use this to return the associated code you require (the alphabetical code) by chopping up the county_desc and re-arranging the pieces.

Let's suppose we have the following data in our table:-

County
-------------------------
county_code | county_desc
-------------------------
SD003         SD-Aurora
SDAUR         Aurora, SD


Therefore, given the code "SD003" we want to return "SDAUR"

In Oracle SQL terms you would write something like this:

SELECT county_code
FROM county
WHERE county_desc =
     (
      SELECT SUBSTR(county_desc,4,LENGTH(county_desc)-3)||
', '||SUBSTR(county_desc,1,2)
      FROM county
      WHERE county_code = :in_county_code
     )


What this does is to chop up the county_desc associated with the given county_code and rearrange it into the format associated with the other county_code. We select the county_desc field starting from the 4th character (as the description is 2 characters followed by a hyphen followed by the part we're interested in) to the end and then add on to this a comma followed by the first 2 characters of the description. This will then match the other format of the county description for the same county and from this row we retrieve the alternative county code.

N.B. We have assumed that the format of 2 character state code followed by a hyphen followed by the county name will never change. In this case this is probably a reasonable assumption but as a general rule you should avoid "magic" numbers in your code. If you were embedding this query in a PL/SQL module you could declare a constant with the value of 4 and use the constant in the query.

You might find this question and answer on subqueries helpful. Also see our Oracle SQL tutorials, and our Oracle training page for information about more formal training.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.