Thread: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Hi all!
can anybody say me what's wrong with this query. I just try to take
unique values from table column and print them in random order

select distinct num from (select 1 as num union select 2 as num union
select 1 as num union select 3) t order by random();
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

thank you

sergey kapustin wrote:
> Hi all!
> can anybody say me what's wrong with this query. I just try to take
> unique values from table column and print them in random order
>
> select distinct num from (select 1 as num union select 2 as num union
> select 1 as num union select 3) t order by random();
> ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select
> list
>
> thank you


select num from
     (select distinct num
     from (select 1 as num union select 2 as num union select 1 as num
union select 3 as num) a) t
order by random();

On Wed, 2006-06-07 at 10:09, sergey kapustin wrote:
> Hi all!
> can anybody say me what's wrong with this query. I just try to take
> unique values from table column and print them in random order
>
> select distinct num from (select 1 as num union select 2 as num union
> select 1 as num union select 3) t order by random();
> ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

You could add another level of sub select here, to put the order by
random() outside the select distinct num.  BUT, there's no need for the
distinct. The unions (not union all) guarantee a uniqe return set to
begin with.

Is this the actual query, or a simplified one?

If it's simplified, could we have the original one, and possible
indented in some way so as to make it easier to read?