CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1
netllama
netllama

Reputation: 381

obtaining unique/distinct values from multiple unassociated columns

I have a table in a postgresql-9.1.x database which is defined as follows:

# \d cms
                                      Table "public.cms"
   Column    |            Type             |                    Modifiers                     
-------------+-----------------------------+--------------------------------------------------
 id          | integer                     | not null default nextval('cms_id_seq'::regclass)
 last_update | timestamp without time zone | not null default now()
 system      | text                        | not null
 owner       | text                        | not null
 action      | text                        | not null
 notes       | text

Here's a sample of the data in the table:

 id  |        last_update         |        system        |   owner   |               action                | 
     notes
 ----+----------------------------+----------------------+-----------+-------------------------------------    +-
----------------
 584 | 2012-05-04 14:20:53.487282 | linux32-test5   | rfell     | replaced MoBo/CPU                   | 
  34 | 2011-03-21 17:37:44.301984 | linux-gputest13 | apeyrovan | System deployed with production GPU | 
 636 | 2012-05-23 12:51:39.313209 | mac64-cvs11     | kbhatt    | replaced HD                         | 
 211 | 2011-09-12 16:58:16.166901 | linux64-test12  | rfell     | HD swap                             | 
drive too small

What I'd like to do is craft a SQL query that returns only the unique/distinct values from the system and owner columns (and filling in NULLs if the number of values in one column's results is less than the other column's results), while ignoring the association between them. So something like this:

 system          |    owner
-----------------+------------------
 linux32-test5   |   apeyrovan
 linux-gputest13 |   kbhatt 
 linux64-test12  |   rfell
 mac64-cvs11     |

The only way that I can figure out to get this data is with two separate SQL queries: SELECT system FROM cms GROUP BY system; SELECT owner FROM cms GROUP BY owner;

Upvotes: 0

Views: 143

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

Far be it from me to inquire why you would want to do such a thing. The following query does this by doing a join, on a calculated column using the row_number() function:

select ts.system, town.owner
from (select system, row_number() over (order by system) as seqnum
      from (select distinct system
            from t
           ) ts
     ) ts full outer join
     (select owner, row_number() over (order by owner) as seqnum
      from (select distinct owner
            from t
           ) town
     ) town
     on ts.seqnum = town.seqnum

The full outer join makes sure that the longer of the two lists is returned in full.

Upvotes: 2

Related Questions