CODEWITHSUNDEEP
sqloracle-databaseselectunion
vr552
vr552

Reputation: 301

Is it possible to UNION distinct rows but disregard one column to determine uniqueness?

select d.id, d.registration_number
from DOCUMENTS d

union

select dd.id, dd.registration_number
from DIFFERENT_DOCUMENTS dd

Would it be possible to union those results based solely on the uniqueness of the registration_number, disregarding the id of the documents?

Or, is it possible to achieve the same result in a different way?

Just to add: actually I'm unioning 5 queries, each ~20 lines long, with 4 columns that should be disregarded in determining uniqueness.

Upvotes: 1

Views: 1111

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Assuming registration_number is unique in each table, you can use not exists:

select d.id, d.registration_number
from DOCUMENTS d
union all
select dd.id, dd.registration_number
from DIFFERENT_DOCUMENTS dd
where not exists (select 1
                  from DOCUMENTS d
                  where dd.registration_number = d.registration_number
                 );

Upvotes: 0

PBD
PBD

Reputation: 61

Since the other answers are already correct, may I ask why do you need to retrieve other columns in that query since the primary purpose appear to gather unique registration numbers?

Wouldn't it be simpler to first gather unique registration number and then retrieve the other info?

Or in your actual query, first gather the info without the columns that should be disregarded and then gather the info in these column if need be?

Like,for example, making a view with

SELECT d.registration_number
  FROM DOCUMENT d
 UNION
SELECT dd.registration_number
  FROM DIFFERENT_DOCUMENT dd

and then gather information using that view and JOINS?

Upvotes: 0

Randy
Randy

Reputation: 16677

you basically need to wrap the unioned data with something else to get only the ones you want.

SELECT min(id), registration_number
FROM   (SELECT id, registration_number
        FROM   documents 
        UNION ALL
        SELECT id, registration_number
        FROM   different_documents) 
GROUP BY registration_number

Upvotes: 2

Mureinik
Mureinik

Reputation: 311018

Union will check the combination of all the columns for uniqueness. You could, however, use union all (that does not remove duplicates) and then apply the logic yourself using the row_number window function:

SELECT id, registration_number
FROM   (SELECT id, registration_number, 
               ROW_NUMBER() OVER (PARTITION BY registration_number ORDER BY id) AS rn
        FROM   (SELECT id, registration_number
                FROM   documents 
                UNION ALL
                SELECT id, registration_number
                FROM   different_documents) u
       ) r
WHERE  rn = 1

Upvotes: 1

Related Questions