Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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.

asked 04 Oct '23, 07:49

Baron's gravatar image

Baron
2.2k140151180
accept rate: 48%

edited 04 Oct '23, 14:26

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

(04 Oct '23, 11:00) Volker Barth
Replies hidden

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.

(04 Oct '23, 14:26) Baron

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...

(05 Oct '23, 01:39) Volker Barth

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.

(05 Oct '23, 02:47) Baron
1

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.

(05 Oct '23, 04:32) Volker Barth

Thank you.

(05 Oct '23, 06:00) Baron
showing 1 of 6 show all flat view

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.

permanent link

answered 04 Oct '23, 18:08

Chris%20Keating's gravatar image

Chris Keating
7.8k49129
accept rate: 31%

converted 05 Oct '23, 04:11

Volker%20Barth's gravatar image

Volker Barth
40.3k363552824

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×5

question asked: 04 Oct '23, 07:49

question was seen: 381 times

last updated: 05 Oct '23, 06:00