Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Pulling the first row with a repeating value

819570Mar 18 2011 — edited Mar 18 2011
Hi,
We're using Oracle 11.1.

We have a table of zip codes.

We have multiple rows for each zip code because we have different neighborhoods that use the same zip code.

We want to reduce the rows to one row per zip code. But they told use they want to keep the fields that differ (ex. neighborhood).

So they said " Just take the first value of each."

This code does reduce the zip codes to one row per zip code but as you can see I've replaced the field values with the field name.
SELECT distinct  ZIPCODE, CITY, STATE, COUNTY, AREACODE, 'CITYTYPE', CITYALIASABBREVIATION, 'CITYALIASNAME', LATITUDE, LONGITUDE, TIMEZONE, ELEVATION, COUNTYFIPS, DAYLIGHTSAVING, PREFERREDLASTLINEKEY, CLASSIFICATIONCODE,
           MULTICOUNTY, STATEFIPS, 'CITYSTATEKEY', 'CITYALIASCODE', 'PRIMARYRECORD', CITYMIXEDCASE, 'CITYALIASMIXEDCASE', STATEANSI, COUNTYANSI
    FROM   zip_code
GROUP BY   ZIPCODE, CITY, STATE, COUNTY, AREACODE, CITYTYPE, CITYALIASABBREVIATION, LATITUDE, LONGITUDE, TIMEZONE, ELEVATION, COUNTYFIPS, DAYLIGHTSAVING, PREFERREDLASTLINEKEY, CLASSIFICATIONCODE, MULTICOUNTY, STATEFIPS,
           CITYALIASCODE, PRIMARYRECORD, CITYMIXEDCASE, STATEANSI, COUNTYANSI
order by zipcode; 
How would I take either one record per zip code with the first value of those fields or

How would I take the first record for each zip code?

either way works for me.

Thanks,
This post has been answered by Frank Kulash on Mar 18 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 15 2011
Added on Mar 18 2011
3 comments
162 views