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

Question

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?

If you consider NULLs as matching values in combinations then you need handle it properly. NULL=NULL is false. See asktom.oracle.com/pls/asktom/… — Serg
– Serg, Commented Jun 30, 2017 at 18:17
Can you give an example of what you are wanting the output to be? which rows in your data set would you not want row IDs for? — Craig
– Craig, Commented Jun 30, 2017 at 19:08
Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Jun 30, 2017 at 20:08
I assume this has nothing to do with PLSQL, so I will edit the title. — user5683823
– user5683823, Commented Jun 30, 2017 at 20:36
"I want to get the row ID's" probably means there are row id's somewhere in the table? Or do you mean the ROWID assigned by Oracle when the table is stored on disk? And, HOW do you want to get the ID's? In what form? What should the output look like? — user5683823
– user5683823, Commented Jun 30, 2017 at 20:39

Jeff Holt · Accepted Answer · 2017-06-30 21:02:19Z

1

@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.

edited Jun 30, 2017 at 21:02

answered Jun 30, 2017 at 20:51

Jeff Holt

3,2623 gold badges27 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

snl330 Over a year ago

I updated the NULL cells to the string 'n/a', since they have the same "meaning" for the data in table. Thanks to you and @Serg for the reminder on NULLs.

RobC · Accepted Answer · 2019-10-19 13:49:48Z

0

Instead of comparing, use the decode function which compares two null values correctly.

e.WHAT = c.WHAT -> DECODE(e.WHAT, c.WHAT, 1) = 1

edited Oct 19, 2019 at 13:49

RobC

25.3k21 gold badges87 silver badges88 bronze badges

answered Oct 19, 2019 at 13:25

Tomasz

1601 silver badge16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related