Oracle 11g - select only one row for each town in a table

Question

I have a table with 300K records, but only ~100 unique town names. I need sql to return 1 row for each individual town name. Table structure:

UNIQUE_ID	STREET_NUMBER	STREET_NAME	STREET_TYPE	TOWN	ZIP
UID01	11	TROY	STREET	ASHFIELD	2017
UID02	13	ABED	ROAD	ASHFIELD	2017
UID03	2	FRANK	COURT	EMERTON	2021
UID04	8	DENNIS	GROVE	SACKVILLE	2028
UID05	97	MAC	CRESCENT	SACKVILLE	2028
UID06	102	CHARLIE	WALK	SACKVILLE	2028
UID07	70	DEE	BOULEVARD	WINDSOR	2033
UID08	27	POPPY	STREET	WINDSOR	2033
UID09	33	ALLY	WAY	BARGO	2315
UID10	48	ELS	AVENUE	BARGO	2315

I'm trying to get the data returned to be something like:

UNIQUE_ID	STREET_NUMBER	STREET_NAME	STREET_TYPE	TOWN	ZIP
UID01	11	TROY	STREET	ASHFIELD	2017
UID03	2	FRANK	COURT	EMERTON	2021
UID04	8	DENNIS	GROVE	SACKVILLE	2028
UID07	70	DEE	BOULEVARD	WINDSOR	2033
UID09	33	ALLY	WAY	BARGO	2315

Don't care which record is returned for each town name, but need one record for each town.

I've trawled through various similar posts but can't seem to get the syntax correct.

I'm able to select each individual town name using this:

select min(TOWN) keep (dense_rank first order by rownum) TOWN
from ADDRESS_TABLE group by TOWN;

But not sure how to get the other attached data to return as well.

Help please?

What is the logic behind which single record for each town gets selected? — Tim Biegeleisen, Aug 10 '22 at 08:12
Does this answer your question? [Select First Row of Every Group in sql](https://stackoverflow.com/questions/16529701/select-first-row-of-every-group-in-sql) — astentx, Aug 10 '22 at 08:15

score 0 · Answer 1 · answered Aug 10 '22 at 08:15

If you don't care about which one to take, then take any of them (e.g. first by unique_id):

WITH
   temp
   AS
      (SELECT unique_id,
              street_number,
              street_name,
              street_type,
              town,
              zip,
              ROW_NUMBER () OVER (PARTITION BY town ORDER BY unique_id) rn
         FROM address_table)
SELECT unique_id,
       street_number,
       street_name,
       street_type,
       town,
       zip
  FROM temp
 WHERE rn = 1

Perfect, this did exactly the trick - thanks heaps Littlefoot! — pleaseandthankyou, Aug 10 '22 at 10:20

Oracle 11g - select only one row for each town in a table

1 Answers1