Thread: Bug, feature, quirk? HELP

I tried the following command

db=> SELECT DISTINCT name, city FROM table ORDER BY trim(table.name);
ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list

As you can tell, it failed. This is a bit annoying for I am relying on a
similar command for one of my projects. Does anyone have any ideas what is
going on? Are there work arounds? Should I just give up for now (i.e. wait
for 7.1+n to come out)?

>I tried the following command
>
>db=> SELECT DISTINCT name, city FROM table ORDER BY trim(table.name);
>ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target
list

Try:

SELECT DISTINCT trim(name) AS name,city FROM table ORDER BY name ;

And if that doesn't work:

SELECT DISTINCT trim(name),city FROM table ORDER BY trim(name) ;

"Stoppel, Brett W" <bstoppel@ku.edu> writes:
> I tried the following command
> db=> SELECT DISTINCT name, city FROM table ORDER BY trim(table.name);
> ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list

> Are there work arounds?

What's wrong with doing what the message suggests?
SELECT DISTINCT trim(name), city FROM table ORDER BY trim(name);

> Should I just give up for now (i.e. wait for 7.1+n to come out)?

Don't hold your breath.  The reason for the restriction is that the
results are ambiguous without it --- maybe not terribly ambiguous in
this particular case, but it's easy to create examples where you have
no idea what values are to be used for the sort.  For example
    SELECT DISTINCT foo, bar FROM table ORDER BY baz;

            regards, tom lane

Thanks Bill, Len, and Tom:

You suggestion of

    select distinct trim(name) as foo .... order by trim(name);

works wonderfully.


I do have another question about this though. Why does it this command work

    select name as foo .... order by trim(name);

when this does not

    select distinct name as foo .... order by trim(name);

Once again, thanks a million.

Brett