|
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. |
|
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. |
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:
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 ) T1What I want is to have DISTINCT applying on two columns, and then have a list over the result.
Something like this:
in other words, I want to change this statement
so that I get:
Michael,John,Raphael,Carthy,Carther
instead of:
Michael,John,Raphael,Carthy,Carther,
MichaelIn both of your suggestions above (as well in my approach B), 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...
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.
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.
Thank you.