How to use distinct keyword on two columns in oracle sql?-CodePudding

I used distinct keyword on one column it did work very well but when I add the second column in select query it doesn't work for me as both columns have duplicate values. So I want to not show me the duplicate values in both columns. Is there any proper select query for that.

The sample data is:
For Col001:

Col002:

I want these data in this format: Col001:

555
7878
89.

Col002:

I tried the following query:

Select distinct col001, col002 from tbl1

CodePudding user response：

Use a set operator. UNION will give you the set of unique values from two subqueries.

select col001 as unq_col_val 
from your_table
union
select col002 
from your_table;

This presumes you're not fussed whether the value comes from COL001 or COL002. If you are fussed, this variant preserves that information:

select  'COL001' as source_col
       ,col001 as unq_col_val 
from your_table
union
select 'COL002' as source_col
       ,col002 
from your_table;

Note that this result set will contain more rows if the same value exists in both columns.

CodePudding user response：

DISTINCT works across the entire row considering all values in the row and will remove duplicate values where the entire row is duplicated.

For example, given the sample data:

CREATE TABLE table_name (col001, col002) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 1, 2 FROM DUAL UNION ALL
SELECT 1, 3 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
--
SELECT 1, 2 FROM DUAL UNION ALL -- These are duplicates
SELECT 2, 2 FROM DUAL;

Then:

SELECT DISTINCT
       col001,
       col002
FROM   table_name

Outputs:

COL001 COL002

1 1

1 2

1 3

2 1

2 2

COL001	COL002
1	1
1	2
1	3
2	1
2	2

And the duplicates have been removed.

If you want to only display distinct values for each column then you need to consider each column separately and can use something like:

SELECT c1.col001,
       c2.col002
FROM   ( SELECT DISTINCT
                col001,
                DENSE_RANK() OVER (ORDER BY col001) AS rnk
         FROM table_name
       ) c1
       FULL OUTER JOIN
       ( SELECT DISTINCT
                col002,
                DENSE_RANK() OVER (ORDER BY col002) AS rnk
         FROM table_name
       ) c2
       ON (c1.rnk = c2.rnk)

Which outputs:

COL001 COL002

1 1

2 2

null 3

COL001	COL002
1	1
2	2
null	3

db<>fiddle here