Select values based on DISTINCT combination of rest of columns Oracle DB

I want to select row IDs associated with distinct column combinations in the remainder of a table. For instance, if the distinct rows are

I want to get the row IDs associated with each row. I can’t query for distinct IDs since they are the row’s primary key (and hence are all distinct).

So far I have:

SELECT e.ID 
FROM E_UPLOAD_TEST e
INNER JOIN (
  SELECT DISTINCT WHAT, MATERIALS, ERROR_FIELD, UNITS, SEASONALITY, DATA_TYPE, DETAILS, METHODS, DATA_FORMAT 
  FROM E_UPLOAD_TEST) c
ON e.WHAT = c.WHAT AND e.MATERIALS = c.MATERIALS AND e.ERROR_FIELD = c.ERROR_FIELD AND e.DATA_TYPE = c.DATA_TYPE AND e.METHODS = c.METHODS AND e.DATA_FORMAT = c.DATA_FORMAT;  

which runs but doesn’t return anything. Am I missing a GROUP BY and/or MIN() statement?

Answer

@serg is correct. Every single row in your example has at least one column value that is null. That means that no row will match your join condition. That is why your query results in no rows found.

Modifying your condition might get you what you want so long has your data isn’t changing frequently. If it is changing frequently, then you probably want a single query for the entire job otherwise you’ll have to set your transaction so that it is immune to data changes.

An example of such a condition change is this: ( (e.WHAT is null and c.WHAT is null) or (e.WHAT = c.WHAT) )

But such a change makes sense only if two rows having a null value in the same column means the same thing for both rows and it has to mean the same thing as time marches on. What “WHAT is null” means today might not be the same thing tomorrow. And that is probably why C. J. Date hates nulls so much.