CODEWITHSUNDEEP
mysqlsql
Ranga Reddy
Ranga Reddy

Reputation: 3066

Get Unique or Distinct values of more than one column

Assume i have Table with more than 2 or 3 three fields. In that field i want to get Unique values from that table.

Input:

Table Name: Test

column1   column2  column3
  A         P        Y
  B         P        X
  A         Q        Z
  C         R        Y
  B         R        Y

Output:

column1    column2   column3
  A          P         Y
  B          Q         X
  C          R         Z

Here order is not import but i want to get unique values from different columns.

Upvotes: 1

Views: 1459

Answers (2)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Simplest approach could be UNION of all the unique values across every column like below.

SELECT DISTINCT column_name,column_value FROM
(
    SELECT 'column1' as column_name,
           Column1 as column_value
    FROM test
    UNION ALL
    SELECT 'column2' as column_name,
           Column2
    FROM test
    UNION ALL
    SELECT 'colum3' as column_name,
           Column3
    FROM test
)

Upvotes: 2

Multisync
Multisync

Reputation: 8787

In oracle you can use this query:

select column1, column2, column3 
from (select rownum rw, column1 from (select distinct column1 from test)) t1 
     full outer join
     (select rownum rw, column2 from (select distinct column2 from test)) t2 on (t2.rw = t1.rw)
     full outer join
     (select rownum rw, column3 from (select distinct column3 from test)) t3 on (t3.rw = t2.rw or t3.rw = t1.rw);

ROWNUM is a pseudocolumn which gives a row number to each row.

Note that if the columns have unequal number of unique values there can be NULL values in some rows/columns.

The idea is to find all unique values in each column (3 subqueries), assign row number to each row in each subquery and full join all the results by row number.

Upvotes: 2

Related Questions