How to use concatenation '||' with distinct clause of SELECT query?

Question

Hi, I get a missing expression error when I try,

SELECT 'label1:'||distinct col1 from tab1;

Is there a way to get around this error? Thanks in advance.

Edit: The entire query is:

SELECT 'label1:'||distinct col1 from tab1 order by col1;

Answer 1

The first error is because distinct is in the wrong place.

SELECT distinct 'label1:'|| col1 as c 
from tab1
ORDER BY c;

The second one mentioned in the comments is because you were ordering by col1. You need to alias the new column and order by the alias as above. (Note you can use col1 as the alias if you want)

Answer 2

DISTINCT is a part of a SELECT clause, not the function of the columns:

SELECT  DISTINCT 'label1:' || col1
FROM    tab1

Update:

To make it work with ORDER BY, use

SELECT  'label1:' || col1
FROM    (
        SELECT  DISTINCT col1
        FROM    tab1
        )
ORDER BY
        col1

Answer 3

try this one

SELECT  DISTINCT 'label1:' || col1
FROM tab1 order by 1;