CODEWITHSUNDEEP
postgresqluniquedistinctmultiple-columns
ptb
ptb

Reputation: 41

PostgreSQL - How to get distinct on two columns separately?

I've a table like this:

Source table "tab"
column1   column2
      x         1
      x         2
      y         1
      y         2
      y         3
      z         3

How can I build the query to get result with unique values in each of two columns separately. For example I'd like to get a result like one of these sets:

column1   column2
      x         1
      y         2
      z         3

or

column1   column2
      x         2
      y         1
      z         3

or ...

Thanks.

Upvotes: 2

Views: 3328

Answers (1)

Rory
Rory

Reputation: 41917

What you're asking for is difficult because it's weird: SQL treats rows as related fields but you're asking to make two separate lists (distinct values from col1 and distinct values from col2) then display them in one output table not caring how the rows match up.

You can so this by writing the SQL along those lines. Write a separate select distinct for each column, then put them together somehow. I'd put them together by giving each row in each results a row number, then joining them both to a big list of numbers.

It's not clear what you want null to mean. Does it mean there's a null in one of the columns, or that there's not the same number of distinct values in each column? This one problem from asking for things that don't match up with typical relational logic.

Here's an example, removing the null value from the data since that confuses the issue, different data values to avoid confusing rowNumber with data and so there are 3 distinct values in one column and 4 in another. This works for SQL Server, presumably there's a variation for PostgreSQL.

if object_id('mytable') is not null drop table mytable;
create table mytable ( col1 nvarchar(10) null, col2 nvarchar(10) null) 
insert into mytable 
            select 'x', 'a'
union all   select 'x', 'b'
union all   select 'y', 'c'
union all   select 'y', 'b'
union all   select 'y', 'd'
union all   select 'z', 'a'

select c1.col1, c2.col2
from 
    -- derived table giving distinct values of col1 and a rownumber column
(   select col1
        , row_number() over (order by col1) as rowNumber 
    from ( select distinct col1 from mytable ) x ) as c1
full outer join 
    -- derived table giving distinct values of col2 and a rownumber column
(   select col2
        , row_number() over (order by col2) as rowNumber 
    from ( select distinct col2 from mytable ) x ) as c2
on c1.rowNumber = c2.rowNumber

Upvotes: 1

Related Questions