I want to retain unique values in one column and remove several rows based on the count of those unique values in oracle sql

I sincerely apologize that if the title of the question is not properly addressing the issue I have, hence I will try to explain here.

The question is related to the last question I asked and I very grateful for the answer I got which got me the result I was looking for.

However, I had just been asked to make some alterations in the output which I had been trying in the weekend using the solution I got, but all in vain.

My data is the same I posted before and that was:

Now what happens is that the first 5 columns come from two tables that are LEFT JOINED but as soon as another table is joined (i tried inner join as well) the whole bridge (first 5 columns) repeats itself entirely based on the number of unique values the last two columns (Name_1 and Proximity) have. I guess its some sort of a cross-join??

What the I am asked to achieve is that my Data should look like:

Now based on the SQL query that I have which is shown below:

    SELECT 
       STRUCTURE_NO, 
       NAME,
       STRUCTURE_TYPE,
       number_of_spans,
       main_span_flag,
       CASE rn WHEN 1 THEN name1 END AS name1,
       CASE rn WHEN 1 THEN PROXIMITY_CODE END AS proximity_code
   FROM   ( 
     SELECT a.STRUCTURE_NO, 
         a.NAME,
         a.STRUCTURE_TYPE,
         a.number_of_spans,
         d.main_span_flag,
         d.span_no,
         e.NAME AS name1,
         e.PROXIMITY_CODE,
       
         ROW_NUMBER() OVER (
           PARTITION BY a.structure_no,e.name,e.proximity_code                      
           ORDER BY d.span_no ASC
         ) AS rn,
         ROW_NUMBER() OVER (
           PARTITION BY d.span_no,e.name                     
           ORDER BY a.structure_no ASC
         ) AS rn1
  FROM   STRUCTURE a
         INNER JOIN STRUCTURE_STATUS_TY b
         ON a.structure_status_type_code=b.structure_status_type_code
         LEFT OUTER JOIN span d
         ON a.structure_id=d.structure_id
         LEFT OUTER JOIN STRUCTURE_FEAT_PROXMTY e
         ON a.STRUCTURE_ID = e.STRUCTURE_ID
         

  ORDER BY a.STRUCTURE_NO ASC 
  )

I tried to create more partitions so that I can develop some sort of a sequence of the numbers and then remove those rows that dont have that sequence. But it has worked.

I apologize as I am not that experienced in SQL hence I would be highly obliged if this cross join or the whole bridge repetition gets removed and I get the required data, if its possible.

Thanks.

> Now what happens is that the first 5 columns come from two tables that are LEFT JOINED but as soon as another table is joined (i tried inner join as well) the whole bridge (first 5 columns) repeats itself entirely based on the number of unique values the last two columns (Name_1 and Proximity) have. I guess its some sort of a cross-join??

This suggests that you need to add more filters to the JOIN condition between the two tables so that you do not get multiple matches for each row in the driving table.

There is not an obvious column to join on in your sample data but you do appear to be matching rows in order of the span_no and proximity_code column so you can generate a ROW_NUMBER for each side of the join and then correlate on that (if appropriate):

SELECT a.STRUCTURE_NO, 
       a.NAME,
       a.STRUCTURE_TYPE,
       a.number_of_spans,
       d.main_span_flag,
       d.span_no,
       e.name AS name1,
       e.proximity_code
FROM   (
  SELECT a.structure_id,
         a.STRUCTURE_NO, 
         a.NAME,
         a.STRUCTURE_TYPE,
         a.number_of_spans,
         d.main_span_flag,
         d.span_no,
         ROW_NUMBER() OVER (
           PARTITION BY a.structure_id
           ORDER BY d.span_no. d.main_span_flag DESC
         ) AS rn
  FROM   STRUCTURE a
         LEFT OUTER JOIN span d
         ON a.structure_id=d.structure_id
  WHERE  EXISTS(
           -- This table is never used, you only check that a match exists.
           SELECT 1
           FROM   STRUCTURE_STATUS_TY b
           WHERE  ad.structure_status_type_code = b.structure_status_type_code
         )
) ad
LEFT OUTER JOIN (
  SELECT structure_id,
         name,
         proximity_code,
         ROW_NUMBER() OVER (
           PARTITION BY structure_id ORDER BY proximity_code
         ) AS rn
  FROM   STRUCTURE_FEAT_PROXMTY
) e
ON     ad.STRUCTURE_ID = e.STRUCTURE_ID
   AND ad.rn = e.rn