distinct over several columns

[Baron]

Is it possible to select 2 columns distinctly in one single select statement?

In this example I have the problem that 'Michael' will be listed twice:

create or replace table employees (firstname varchar(100), lastname varchar(100), phone varchar(100));
insert into employees values 
('Michael', 'Carthy', '01123456'),
('John', 'Carther', '01654321'),
('Raphael', 'Michael', '01654123');
select list (distinct firstname) || ',' ||  list (distinct lastname) from employees

One way to solve the problem is something like this (approach B):

select list(distinct f1) from
(select firstname f1 from employees 
union 
select lastname f1 from employees) T1

But I want to avoid the second select statement, since the table employees enough big is.

[VolkerBarth]

Do you want a LIST of two distinct columns (according to your sample queries) or just a SELECT DISTINCT over two columns (according to your question)?

The latter (UNION DISTINCT) is the default for UNION unless used with UNION ALL, as your sample query shows:

select firstname f1 from employees 
union 
select lastname f1 from employees

If you want a DISTINCT LIST, I guess your sample is the way to go - but you could add a UNION ALL here to prevent two DISTINCT operations, such as

select list(distinct f1) from
   (select firstname f1 from employees 
    union all
    select lastname f1 from employees
   ) T1

[Baron]

What I want is to have DISTINCT applying on two columns, and then have a list over the result.

Something like this:

select list(distinct(firstname, lastname)) from employees

in other words, I want to change this statement

select list (distinct firstname) || ',' ||  list (distinct lastname) from employees

so that I get:

Michael,John,Raphael,Carthy,Carther

instead of:

Michael,John,Raphael,Carthy,Carther,Michael

In both of your suggestions above (as well in my approach B), the whole table employees will be scanned twice, which I dont want.

[VolkerBarth]

the whole table employees will be scanned twice, which I dont want.

Whether that is true will depend on indexes on these two columns. But of course, a query without a filter won't be able to evaluate two different columns of a table without having to access all these values, so I don't seem to get your point...

[Baron]

One more problem is that employees can be a procedure (and not a table), which means that the second query on the same procedure may result different output.

[VolkerBarth]

If that is an issue, I would use a local temporary table (with NOT TRANSACTIONAL) to store the result set (including both columns) of your one procedure call, and then use that temporary table as source for the "SELECT LIST(DISTINCT MyColumn) FROM (... UNION ALL...)" query.

[Baron]

Thank you.

[chris_keating · Accepted solution]

DISTINCT removes duplicate rows from a result set but does not operate at a individual column level in a result set. The UNION is a reasonable solution based on the requirement.