ORA-01791: not a SELECTed expression

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may get the following error when you combine DISTINCT and ORDER BY clauses in a query as follows:

select distinct
agent,
application_id
from apex_workspace_activity_log
order by view_date;

ORA-01791: not a SELECTed expression

The problem is caused by the presence of column view_date in the ORDER BY clause that is the expression mentioned in the error message. The term expression is probably used because one can specify expressions in the ORDER BY, not just simple columns.

The query indeed will run without problems if the ORDER BY clause contains SELECTed columns or expressions:

select distinct
agent,
application_id
from apex_workspace_activity_log
order by application_id;

or

select distinct
agent,
application_id,
trunc(view_date)
from apex_workspace_activity_log
order by application_id, trunc(view_date);

Note that it's also possible to specify expressions based on the SELECTed columns that are not explicitly calculated in the SELECT list:

select distinct
agent,
application_id,
application_name,
trunc(view_date)
from apex_workspace_activity_log
order by upper(application_name), application_id, trunc(view_date);

But in the end, is either of these alternate queries returning an acceptable result as far as your application is concerned?
How can i turn the original (illegal) query into an acceptable form?

Over the years I've seen people using the DISTINCT clause quite arbitrarily and most of them had an MS-Access background...
Don't ask me if the two things were correlated ;-)
It looks like they just want to be sure that there are no duplicates in the resulting recordset, so they add DISTINCT even if they don't really need to do that. If that is the case, then the solution is easy, just get rid of this keyword.
However the query above seems to be an illegal representation of a legitimate (albeit strange) requirement:
i'd like to retrieve unique pairs of application IDs and user agents in order of appearance in the log.
If this is the correct interpretation, then DISTINCT is useless and GROUP BY is the answer.

select agent, application_id
from (select
       agent,
       application_id,
       min(view_date) d
       from apex_workspace_activity_log
       group by agent, application_id)
order by d;

In conclusion I'd say that ORA-01791 is either the symptom of a trivial error or the ringing bell of an ill-designed query.

See message translations for ORA-01791 and search additional resources.