Re: Distinct in query results in wrong order by

Originally posted by Roelof
> We have query like this
>
> select distinct to_char(date_column, 'day dd mm yyyy')
> , next column
> from table
> order by date_colum
>
> It runs OK without the distinct. But with distinct it returns "not a
> selected expression". The next query runs. But it sorts om the name of
> the days insted of the date_column value as intended.
>
> select distinct to_char(date_column, 'day dd mm yyyy')
> , next column
> from table
> order by to_char(date_column, 'day dd mm yyyy')
>
> Help would be highly appreceated.
>
> Roelof

The only values you can use in the ORDER BY are to_char(date_column, 'day dd mm yyyy') and next column. But you can perform functions on those values, so this will work:

select distinct to_char(date_column, 'day dd mm yyyy') , next column
from table
order by to_date(to_char(date_column, 'day dd mm yyyy'), 'day dd mm yyyy');

--
Posted via http://dbforums.com